오라클 split 쿼리로 해결하기

 
--오라클 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