[Oracle] 문자열 구분자를 행으로 분리 (Split)

오라클 쿼리에서 칼럼에 구분자로 입력된 값을 행으로 분리(Split)하여 조회해야 할 상황이 종종 발생한다. 대부분 값을 조회하여 프로그래밍 코드에서 구분자를 분리하는 작업을 한다. 그러나 쿼리에서 미리 행으로 분리해야 하는 경우도 발생하므로 아래의 예제를 참고하면 된다. 부하가 많이 발생할 수 있으므로 데이터가 많은 경우는 권장하지 않는다.

 

오라클 문자열 구분자 SPLIT

예제 1 - Oracle 10g 이상 (정규식 함수 사용)

 SELECT REGEXP_SUBSTR(a.langList, '[^|]+', 1, LEVEL) AS split_result
   FROM (SELECT 'Java|Kotlin|Python|Swift' AS langList 
           FROM dual) a
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(a.langList, '[^|]+','')) + 1

 

 

위의 예제는 구분자(|)를 기준으로 문자열('Java|Kotlin|Python|Swift')을 행으로 분리하는 예제이다.

정규식 함수를 사용했으므로 오라클 10g 이상에서 사용할 수 있다.

 

예제 2 - Oracle 8i 이상

 SELECT SUBSTR(a.langList, 
            INSTR(a.langList, '|', 1, LEVEL) + 1,
            INSTR(a.langList, '|', 1, LEVEL + 1) - INSTR(a.langList, '|', 1, LEVEL)-1
        ) AS split_result
   FROM (SELECT '|' || 'Java|Kotlin|Python|Swift' || '|' AS langList 
           FROM dual) a
CONNECT BY LEVEL <= LENGTH(a.langList) - LENGTH(REPLACE(a.langList, '|')) - 1

 

 

예제1과 동일한 기능을 하는 쿼리이다. 정규식 함수를 사용하지 않았으므로 대부분의 오라클 버전에서 사용 가능하다.

예제1보다는 조금 쿼리문이 복잡해 보이지만 기능은 잘 작동한다.

 

응용 예제

WITH emp(ename, lang) AS (
    SELECT 'CLARK', 'C,C++,C#'               FROM dual UNION ALL
    SELECT 'JONES', 'Python,Julia,R'         FROM dual UNION ALL 
    SELECT 'SCOTT', 'Java,Kotlin,JavaScript' FROM dual   
)

SELECT a.ename
     , REGEXP_SUBSTR(a.lang,'[^,]+', 1, b.lv) AS lang_nm
  FROM emp a 
     , (SELECT LEVEL AS lv 
          FROM dual 
       CONNECT BY LEVEL <= 10) b --구분자(,) 최대 개수 10개
 WHERE REGEXP_SUBSTR(a.lang, '[^,]+', 1, b.lv) IS NOT NULL
 ORDER BY ename, lang_nm

 

 

예제1의 정규식 함수를 조금 더 응용하여 실제 테이블 칼럼의 값을 행으로 분리하는 예제이다.

구분자 쉼표(,)를 기준으로 문자열을 잘라서 행으로 분리하였다.

 

칼럼에 사용된 구분자(,)의 개수만큼 가상의 행을 만드는 쿼리문이다. 문자열에 구분자의 개수가 많다면 10개를 100개로 변경해도 되지만 너무 값이 크면 해당 개수만큼 쿼리가 실행되므로 부하가 발생할 수 있다.

(SELECT LEVEL AS lv 
FROM dual 
CONNECT BY LEVEL <= 10) b --구분자(,) 최대 개수 10개 (변경 가능)

 

구분자를 10번 잘랐을 때 값이 없는 경우는 제외한다.

WHERE 부분을 삭제하고 실행하면 정확한 의미를 알 수 있다.

WHERE REGEXP_SUBSTR(a.lang, '[^,]+', 1, b.lv) IS NOT NULL

 

[Oracle] 오라클 콤마를 열, 행으로 분리하는 방법

[Oracle] 오라클 REGEXP_SUBSTR 함수 사용법