[Oracle] SPLIT

 

     
          
-- ORACLE SPLIT QUERY
    
-- 01. 문자
SELECT CODE_ARR
  FROM (           
        SELECT SUBSTRB( :AS_LIST
		              , INSTRB(','||:AS_LIST, ',', 1, ROWNUM)
                      , INSTRB(:AS_LIST||',', ',', LEVEL) - INSTRB(','||:AS_LIST, ',', 1, ROWNUM)
                      ) AS CODE_ARR
           FROM DUAL
          WHERE 1=1
            AND REPLACE(:AS_LIST,',','') IS NOT NULL
            AND INSTRB(:AS_LIST||',', ',', LEVEL) = LEVEL
          CONNECT BY LEVEL <= LENGTH(:AS_LIST) + LENGTH(',')
        )
  WHERE 1=1
    AND CODE_ARR IS NOT NULL
     
     
-- 02. LOB DATA
SELECT CODE_ARR
  FROM (
        SELECT DBMS_LOB.SUBSTR( :AS_LIST
		                      , DBMS_LOB.INSTR(:AS_LIST || ',', ',', LEVEL) - DBMS_LOB.INSTR(',' || :AS_LIST, ',', 1, ROWNUM)
                              , DBMS_LOB.INSTR(',' || :AS_LIST, ',', 1, ROWNUM)
                      ) AS CODE_ARR
          FROM DUAL
         WHERE 1=1
           AND DBMS_LOB.INSTR(:AS_LIST || ',', ',', LEVEL) = LEVEL
         CONNECT BY LEVEL <= LENGTH(:AS_LIST) + LENGTH(',')
       )
 WHERE 1=1
   AND CODE_ARR IS NOT NULL
     
     
-- 03. 정규식
SELECT REGEXP_SUBSTR(CODE_ARR, '[^,]+', 1, LEVEL) AS CODE_ARR
  FROM (
		SELECT :AS_LIST AS CODE_ARR
		  FROM DUAL
	   )
 WHERE 1=1
 CONNECT BY REGEXP_SUBSTR(CODE_ARR, '[^,]+', 1, LEVEL) IS NOT NULL