oracle unpivot string comma separate to row

오라클에서 테스트되었습니다.

UI에서 comma로 연결된 argument를 입력받아

분리 후 행으로 변환하여 조건으로 공급하는 SQL에 응용하였던 적이 있습니다.

 

WITH T1 ( CN ) 
AS (
SELECT ','||TRIM('A1,A2,A3,A4,') FROM DUAL
)
SELECT 
    SUBSTR(T1.CN,INSTR(T1.CN,',',1,LEVEL)+1,INSTR(T1.CN,',',1,LEVEL+1)-INSTR(T1.CN,',',1,LEVEL)-1)CN
FROM T1 
    CONNECT BY LEVEL <= LENGTH(T1.CN)-LENGTH(REPLACE(T1.CN,','))-1;

WITH T1 ( CN )
AS (
SELECT 'A1,A2,A3,A4,' FROM DUAL
)
SELECT 
    REGEXP_SUBSTR(T1.CN, '[^,]+', 1, LEVEL) AS CN
FROM T1  CONNECT BY LEVEL <= length(t1.cn) - length(replace(t1.cn, ',', ''))

결과는 아래와 같습니다.