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

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

▶ 예제 코드 (SQL)


SELECT
    SUBSTR
    (
        A.SOURCE_STRING
       ,INSTR(A.SOURCE_STRING, '|', 1, LEVEL) + 1
       ,INSTR(A.SOURCE_STRING, '|', 1, LEVEL + 1) - INSTR(A.SOURCE_STRING, '|', 1, LEVEL) - 1
    ) SOURCE_ELEMENT
FROM (SELECT '|' || 'A|B|C|D' || '|' SOURCE_STRING FROM DUAL) A
CONNECT BY LEVEL <= LENGTH(A.SOURCE_STRING) - LENGTH(REPLACE(A.SOURCE_STRING, '|')) - 1

SELECT

SUBSTR

(

A.SOURCE_STRING

,INSTR(A.SOURCE_STRING, '|', 1, LEVEL) + 1

,INSTR(A.SOURCE_STRING, '|', 1, LEVEL + 1) - INSTR(A.SOURCE_STRING, '|', 1, LEVEL) - 1

) SOURCE_ELEMENT

FROM (SELECT '|' || 'A|B|C|D' || '|' SOURCE_STRING FROM DUAL) A

CONNECT BY LEVEL <= LENGTH(A.SOURCE_STRING) - LENGTH(REPLACE(A.SOURCE_STRING, '|')) - 1