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

오라클 SQL에서 하나의 칼럼에 있는 문자열을 콤마(구분자)를 기준으로 열(가로) 또는 행(세로)으로 분리하기 위해서는 REGEXP_SUBSTR 정규식 함수를 사용하면 쉽게 해결할 수 있다. 문자열에 포함될 수 있는 구분자의 최대 개수는 미리 정의가 되어 있어야 쿼리문의 결과가 조금 더 정확하게 조회될 수 있다.

 

목차 콤마를 열로 분리하는 방법 콤마를 행으로 분리하는 방법

 

콤마를 열로 분리하는 방법

WITH temp AS 
(
    SELECT 'AAA,BBB,CCC' str FROM dual UNION ALL
    SELECT '111,222,333' str FROM dual
)

SELECT str
     , REGEXP_SUBSTR(str, '[^,]+', 1, 1) AS str1
     , REGEXP_SUBSTR(str, '[^,]+', 1, 2) AS str2
     , REGEXP_SUBSTR(str, '[^,]+', 1, 3) AS str3
  FROM temp

 

 

REGEXP_SUBSTR 정규식 함수를 사용하여 하나의 칼럼 값을 구분자를 기준으로 여러 개의 칼럼으로 분리할 수 있다.

 

분리할 칼럼의 최대 개수만큼 SELECT 절에 작성해 놓아야 하며, REGEXP_SUBSTR 함수는 자세한 사용법은 하단의 포스팅 링크를 참조하면 도움이 될 것이다.

 

콤마를 행으로 분리하는 방법

WITH temp AS 
(
    SELECT 'AAA,BBB,CCC' str FROM dual UNION ALL
    SELECT '111,222,333' str FROM dual
)

SELECT a.str
     , REGEXP_SUBSTR(a.str, '[^,]+', 1, b.row_cnt) AS val
  FROM temp a
     , (SELECT LEVEL row_cnt FROM dual CONNECT BY LEVEL <= 10) b
 WHERE b.row_cnt <= REGEXP_COUNT(a.str, ',') + 1     
 ORDER BY str, val

 

 

구분자를 행으로 분리하기 위해서는 CONNECT BY절을 사용하여 구분자 최대 개수만큼 빈 행을 생성해 놓아야 한다. 그리고 REGEXP_COUNT 함수를 사용하여 문자열의 구분자 건수를 계산하여 행으로 분리할 수 있다.

 

REGEXP_COUNT 함수는 오라클 11g 이상부터 사용할 수 있으며, 하위 버전에서는 아래의 방법을 사용하면 된다.

 

WITH temp AS 
(
    SELECT 'AAA,BBB,CCC' str FROM dual UNION ALL
    SELECT '111,222,333' str FROM dual
)

SELECT a.str
     , REGEXP_SUBSTR(a.str, '[^,]+', 1, b.row_cnt) AS val
  FROM temp a
     , (SELECT LEVEL row_cnt FROM dual CONNECT BY LEVEL <= 10) b
 WHERE b.row_cnt <= LENGTH(a.str) - LENGTH(REPLACE(a.str, ',')) + 1     
 ORDER BY str, val

 

 

LENGTH 함수와 REPLACE 함수를 사용하여 문자열에 포함된 특정 문자의 개수를 구할 수 있다.

위의 예제와 동일한 결과가 조회되는 것을 확인할 수 있다.

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

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

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