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

■ 구분자를 사용해 문자열을 분리하는 방법을 보여준다.

 

▶ 예제 코드 (SQL)

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
)
SELECT
    A.ID
   ,B.SOURCE_ELEMENT
FROM SOURCE_TABLE A,
(
    SELECT DISTINCT REGEXP_SUBSTR(C.SOURCE_ELEMENT, '[^|]+', 1, LEVEL) SOURCE_ELEMENT
    FROM
    (
        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;