[DATABASE/ORACLE] 구분자를 사용해 문자열 분리하기

 

WITH SOURCE_TABLE AS

(

    SELECT 1 ID, 'A|B|C' SOURCE_STRING FROM DUAL UNION ALL

    SELECT 2 ID, 'B|C'   SOURCE_STRING FROM DUAL UNION ALL

    SELECT 3 ID, 'A|C'   SOURCE_STRING FROM DUAL UNION ALL

    SELECT 4 ID, 'A|B|C' SOURCE_STRING FROM DUAL UNION ALL

    SELECT 5 ID, 'B|C'   SOURCE_STRING FROM DUAL UNION ALL

    SELECT 6 ID, 'B|C|D' SOURCE_STRING FROM DUAL

)

    A.ID

   ,B.SOURCE_ELEMENT

FROM SOURCE_TABLE A,

(

    SELECT DISTINCT REGEXP_SUBSTR(C.SOURCE_ELEMENT, '[^|]+', 1, LEVEL) SOURCE_ELEMENT

    (

        SELECT REPLACE(WM_CONCAT(DISTINCT D.SOURCE_STRING), ',', '|') SOURCE_ELEMENT

        FROM SOURCE_TABLE D

    ) C

    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C.SOURCE_ELEMENT, '[^|]+', '')) + 1

) B

WHERE INSTR('|' || A.SOURCE_STRING || '|',  '|' || B.SOURCE_ELEMENT || '|') > 0

ORDER BY A.ID, B.SOURCE_ELEMENT;