DBMS/오라클]Oracle에서_Split_-_쿼리로_만들기

출처 : http://syronia.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-Split-%EA%B8%B0%EB%8A%A5-%EA%B5%AC%ED%98%84%ED%95%9C-Query




오라클 FUNCTION기능을 이용하는 방법도 있지만 쿼리로 구현해보았다.

구분자가 |일 경우 ------------------------

SELECT substr(wdata,
                      instr(wdata, '|', 1, LEVEL) + 1,
                      instr(wdata, '|', 1, LEVEL + 1) - instr(wdata, '|', 1, LEVEL) - 1) name
FROM (
           SELECT '|' || 'Kim|Lee|Park' || '|' wdata
           FROM DUAL
          )
CONNECT BY LEVEL <= length(wdata) - length(REPLACE(wdata, '|')) - 1


만약 구분자가 2개라면 ---------------

SELECT substr(wdata,
                      instr(wdata, '||', 1, LEVEL) + 2,
                      instr(wdata, '||', 1, LEVEL + 1) - instr(wdata, '||', 1, LEVEL) - 2) id
FROM (
           SELECT '||' || 'babo||kim||hello||home' || '||' wdata
           FROM dual
          )
CONNECT BY LEVEL <= (length(wdata) - length(REPLACE(wdata, '||')))/2 - 1