https://dbstudy.co.kr/w/index.php?action=history&feed=atom&title=SQL_SYS.ODCIVARCHAR2LIST%EC%9D%B4%EC%9A%A9_unionall_%EB%8C%80%EC%B2%B4 SQL SYS.ODCIVARCHAR2LIST이용 unionall 대체 - 편집 역사 2026-05-08T22:48:15Z 이 문서의 편집 역사 MediaWiki 1.39.10 https://dbstudy.co.kr/w/index.php?title=SQL_SYS.ODCIVARCHAR2LIST%EC%9D%B4%EC%9A%A9_unionall_%EB%8C%80%EC%B2%B4&diff=1491&oldid=prev Dbstudy: /* SYS.ODCIVARCHAR2LIST이용 union all 대체 */ 2025-09-25T01:22:27Z <p><span dir="auto"><span class="autocomment">SYS.ODCIVARCHAR2LIST이용 union all 대체</span></span></p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="ko"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← 이전 판</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">2025년 9월 25일 (목) 10:22 판</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l11">11번째 줄:</td> <td colspan="2" class="diff-lineno">11번째 줄:</td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>&lt;source lang=sql&gt;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>&lt;source lang=sql&gt;</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>SELECT regexp_substr(column_value,&#039;[^|]+<del style="font-weight: bold; text-decoration: none;">]</del>&#039;,1,1) code -- &#039;|&#039;구분자 기준 1번째값</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>SELECT regexp_substr(column_value,&#039;[^|]+&#039;,1,1) code -- &#039;|&#039;구분자 기준 1번째값</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>     , regexp_substr(column_value,&#039;[^|]+<del style="font-weight: bold; text-decoration: none;">]</del>&#039;,1,2) code_name -- &#039;|&#039;구분자 기준 2번째값   </div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>     , regexp_substr(column_value,&#039;[^|]+&#039;,1,2) code_name -- &#039;|&#039;구분자 기준 2번째값   </div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>   FROM TABLE(SYS.ODCIVARCHAR2LIST(&#039;1|one&#039;, &#039;2|two&#039;, &#039;3|three&#039;));</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>   FROM TABLE(SYS.ODCIVARCHAR2LIST(&#039;1|one&#039;, &#039;2|two&#039;, &#039;3|three&#039;));</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr> </table> Dbstudy https://dbstudy.co.kr/w/index.php?title=SQL_SYS.ODCIVARCHAR2LIST%EC%9D%B4%EC%9A%A9_unionall_%EB%8C%80%EC%B2%B4&diff=1490&oldid=prev Dbstudy: 새 문서: == SYS.ODCIVARCHAR2LIST이용 union all 대체 == * CTE 구문 <source lang=sql> SELECT '1' code, 'one' code_name FROM dual UNION ALL SELECT '2' code, 'two' code_name FROM dual UNION ALL SELECT '3' code, 'three' code_name FROM dual; </source> * regexp_substr함수 와 SYS.ODCIVARCHAR2LIST함수 이용 union all 대체 <source lang=sql> SELECT regexp_substr(column_value,'[^|]+]',1,1) code -- '|'구분자 기준 1번째값 , regexp_substr(column_value,'[^|]+]',1,2) cod... 2025-09-25T01:21:45Z <p>새 문서: == SYS.ODCIVARCHAR2LIST이용 union all 대체 == * CTE 구문 &lt;source lang=sql&gt; SELECT &#039;1&#039; code, &#039;one&#039; code_name FROM dual UNION ALL SELECT &#039;2&#039; code, &#039;two&#039; code_name FROM dual UNION ALL SELECT &#039;3&#039; code, &#039;three&#039; code_name FROM dual; &lt;/source&gt; * regexp_substr함수 와 SYS.ODCIVARCHAR2LIST함수 이용 union all 대체 &lt;source lang=sql&gt; SELECT regexp_substr(column_value,&#039;[^|]+]&#039;,1,1) code -- &#039;|&#039;구분자 기준 1번째값 , regexp_substr(column_value,&#039;[^|]+]&#039;,1,2) cod...</p> <p><b>새 문서</b></p><div>== SYS.ODCIVARCHAR2LIST이용 union all 대체 ==<br /> * CTE 구문 <br /> &lt;source lang=sql&gt;<br /> SELECT &#039;1&#039; code, &#039;one&#039; code_name FROM dual UNION ALL<br /> SELECT &#039;2&#039; code, &#039;two&#039; code_name FROM dual UNION ALL <br /> SELECT &#039;3&#039; code, &#039;three&#039; code_name FROM dual;<br /> &lt;/source&gt;<br /> <br /> <br /> * regexp_substr함수 와 SYS.ODCIVARCHAR2LIST함수 이용 union all 대체 <br /> &lt;source lang=sql&gt;<br /> <br /> SELECT regexp_substr(column_value,&#039;[^|]+]&#039;,1,1) code -- &#039;|&#039;구분자 기준 1번째값<br /> , regexp_substr(column_value,&#039;[^|]+]&#039;,1,2) code_name -- &#039;|&#039;구분자 기준 2번째값 <br /> FROM TABLE(SYS.ODCIVARCHAR2LIST(&#039;1|one&#039;, &#039;2|two&#039;, &#039;3|three&#039;));<br /> <br /> &lt;/source&gt;<br /> <br /> [[category:oracle]]</div> Dbstudy