Oracle 에서 구분자 문자열을 행으로 나누기 — 테이블 함수와 SQL 만으로 하는 방법

화면에서 여러 값을 '1989,1995,2012' 처럼 쉼표로 이어 넘기면, SQL 에서는 이걸 행으로 풀어야 IN 절이나 조인에 쓸 수 있다. Oracle 에는 내장 split 함수가 없어서(23ai 도 마찬가지) 직접 만들어 쓴다.

예제 데이터는 과목별 수업 교시다.

CREATE TABLE CLASS_TIME (SBJ VARCHAR2(20), TM VARCHAR2(100));
INSERT INTO CLASS_TIME VALUES ('수학', '1교시,2교시,3교시');
INSERT INTO CLASS_TIME VALUES ('과학', '7교시,8교시,9교시');

가장 짧은 방법: SQL 한 문장

문자열 하나를 나누는 건 함수 없이 된다.

SELECT LEVEL AS RN
     , TRIM(REGEXP_SUBSTR(:STR, '([^,]*)(,|$)', 1, LEVEL, NULL, 1)) AS TEXT
  FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(:STR, ',') + 1;

:STR = '1교시,2교시,3교시' 이면 1교시·2교시·3교시 세 행이 나온다. REGEXP_SUBSTR 의 마지막 인자 1 은 정규식의 첫 번째 괄호 ([^,]*) 부분만 돌려 달라는 뜻이다.

테이블의 각 행을 나눌 때는 12c 부터 쓸 수 있는 CROSS APPLY / OUTER APPLY 로 행마다 따로 펼친다.

SELECT A.SBJ, B.RN, B.TEXT
  FROM CLASS_TIME A
 OUTER APPLY (
        SELECT LEVEL AS RN
             , TRIM(REGEXP_SUBSTR(A.TM, '([^,]*)(,|$)', 1, LEVEL, NULL, 1)) AS TEXT
          FROM DUAL
       CONNECT BY LEVEL <= REGEXP_COUNT(A.TM, ',') + 1
       ) B;

SBJ	RN	TEXT
수학	1	1교시
수학	2	2교시
수학	3	3교시
과학	1	7교시
과학	2	8교시
과학	3	9교시

APPLY 없이 FROM CLASS_TIME CONNECT BY LEVEL <= ... 로 여러 행을 한꺼번에 펼치면 행끼리 계층이 엮여 결과가 폭발적으로 늘어난다. 여러 행을 나눌 때는 꼭 행마다 따로 펼친다.

흔한 버그: [^,]+ 는 빈 값을 건너뛴다

인터넷에 많이 도는(그리고 원래 내 글에도 있던) 패턴은 REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) 이다. 이 패턴은 비어 있는 항목을 건너뛴다.

-- 'A,,C' 를 나누면
-- '[^,]+'          → A, C, (null)     ← 두 번째 자리가 C 로 당겨진다
-- '([^,]*)(,|$)'   → A, (null), C     ← 자리가 유지된다

순서(자리)가 의미 있는 데이터, 예를 들어 코드 목록과 이름 목록을 같은 순서로 넘겨 짝을 맞추는 경우에는 값이 엉뚱하게 짝지어진다. 빈 값을 버려도 되는 경우가 아니라면 ([^,]*)(,|$) 를 쓴다.

JSON_TABLE 로 나누기

12c 이상이면 문자열을 JSON 배열로 바꿔 JSON_TABLE 에 넘기는 방법도 있다. FOR ORDINALITY 로 순번도 바로 나온다.

SELECT A.SBJ, J.RN, TRIM(J.TEXT) AS TEXT
  FROM CLASS_TIME A
     , JSON_TABLE(
           '["' || REPLACE(A.TM, ',', '","') || '"]', '$[*]'
           COLUMNS (
               RN   FOR ORDINALITY,
               TEXT VARCHAR2(100) PATH '$'
           )
       ) J;

정규식 없이 나눌 수 있지만, 값 안에 " 나 \ 가 들어 있으면 JSON 이 깨진다. 그런 값이 들어올 수 있으면 쓰지 않는다.

재사용하려면: 파이프라인 테이블 함수

여러 곳에서 쓰려고 원래 만든 것이 파이프라인 테이블 함수다. 반환할 행의 타입, 그 컬렉션 타입, 함수 순서로 만든다.

-- 1. 행 타입
CREATE OR REPLACE TYPE TY_SPLIT_ROW AS OBJECT (
    RN   NUMBER,
    TEXT VARCHAR2(4000)
);
/

-- 2. 컬렉션 타입
CREATE OR REPLACE TYPE TB_SPLIT_ROW AS TABLE OF TY_SPLIT_ROW;
/

-- 3. 함수
CREATE OR REPLACE FUNCTION TF_SPLIT_TO_ROW (
    P_DATA IN VARCHAR2,
    P_SEP  IN VARCHAR2 DEFAULT ','
) RETURN TB_SPLIT_ROW PIPELINED
IS
    L_START PLS_INTEGER := 1;
    L_POS   PLS_INTEGER;
    L_RN    PLS_INTEGER := 0;
BEGIN
    IF P_DATA IS NULL THEN
        RETURN;                                   -- 빈 입력은 0행
    END IF;
    IF P_SEP IS NULL THEN
        PIPE ROW (TY_SPLIT_ROW(1, TRIM(P_DATA))); -- 구분자가 없으면 통째로 1행
        RETURN;
    END IF;

    LOOP
        L_RN  := L_RN + 1;
        L_POS := INSTR(P_DATA, P_SEP, L_START);
        IF L_POS = 0 THEN                         -- 마지막 항목
            PIPE ROW (TY_SPLIT_ROW(L_RN, TRIM(SUBSTR(P_DATA, L_START))));
            EXIT;
        END IF;
        PIPE ROW (TY_SPLIT_ROW(L_RN, TRIM(SUBSTR(P_DATA, L_START, L_POS - L_START))));
        L_START := L_POS + LENGTH(P_SEP);
    END LOOP;
    RETURN;
END;
/

원래 함수는 안에서 CONNECT BY + REGEXP_SUBSTR 쿼리를 돌렸는데, INSTR·SUBSTR 로 바꿨다. 그러면

• 빈 항목이 사라지지 않고,
• 구분자를 정규식 문자 클래스('[^' || 구분자 || ']+')에 끼워 넣지 않으므로 |, -, ] 같은 문자나 '||' 처럼 두 글자 이상인 구분자도 그대로 쓸 수 있고,
• 함수 안에서 SQL 을 다시 부르지 않는다.

쓰는 법

-- 1. 문자열 하나 (12.2 부터 TABLE( ) 은 생략 가능)
SELECT TEXT FROM TF_SPLIT_TO_ROW('1교시,2교시,3교시');

-- 2. 테이블의 각 행을 나누기
SELECT A.SBJ, B.RN, B.TEXT
  FROM CLASS_TIME A
  LEFT OUTER JOIN TABLE(TF_SPLIT_TO_ROW(A.TM)) B
    ON 1 = 1;

-- 3. IN 절
SELECT *
  FROM EVENTS
 WHERE YYYY IN (SELECT TEXT FROM TF_SPLIT_TO_ROW('1989,1995,2012'));

-- 4. 다른 구분자
SELECT TEXT FROM TF_SPLIT_TO_ROW('A|B|C', '|');

TM 이 NULL 인 행은 함수가 0행을 돌려주지만, LEFT OUTER JOIN 이라 원래 행은 TEXT 가 NULL 인 채로 남는다.

테이블 함수를 IN 절에 쓸 때 주의

옵티마이저는 테이블 함수가 몇 행을 돌려줄지 모른다. 이럴 때는 블록 크기에 따른 기본 추정치(8KB 블록이면 8168행)로 계획을 세우는 경우가 많아서, 실제로는 값 서너 개인데 큰 테이블과의 조인 방식이 엉뚱하게 잡힐 수 있다. 큰 테이블에 붙일 때는 실행 계획을 꼭 확인한다.

고치거나 지울 때

타입을 함수가 쓰고 있어서, 타입 구조를 바꾸려면 함수 → 컬렉션 타입 → 행 타입 순으로 지우고 다시 만들어야 한다.

DROP FUNCTION TF_SPLIT_TO_ROW;
DROP TYPE TB_SPLIT_ROW;
DROP TYPE TY_SPLIT_ROW;

나는 이 순서와 타입 생성문을 함수 머리 주석에 적어 두고 쓴다. 다음에 고칠 때 찾을 필요가 없다.

19c 이상이면: SQL 매크로

타입 두 개를 따로 관리하는 게 번거롭다면 SQL 매크로가 있다. 함수가 SQL 문장을 돌려주면 Oracle 이 그 문장을 쿼리 안에 펼쳐 넣는다. 테이블 매크로는 21c 기능이지만 19c 에도 19.7 릴리스 업데이트부터 들어왔다.

CREATE OR REPLACE FUNCTION SPLIT_ROWS (P_DATA VARCHAR2)
RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
    RETURN q'{
        SELECT LEVEL AS RN
             , TRIM(REGEXP_SUBSTR(P_DATA, '([^,]*)(,|$)', 1, LEVEL, NULL, 1)) AS TEXT
          FROM DUAL
       CONNECT BY LEVEL <= REGEXP_COUNT(P_DATA, ',') + 1
    }';
END;
/

SELECT * FROM SPLIT_ROWS('1교시,2교시,3교시');

돌려주는 문장 안의 P_DATA 는 호출할 때 넘긴 값으로 바뀐다. 실행되는 것은 결국 순수 SQL 이라 PL/SQL 과 SQL 을 오가는 비용이 없다. 자세한 내용은 Oracle 공식 블로그의 How to split comma separated value strings into rows 에 잘 정리돼 있다.

여러 목록을 같은 자리끼리 짝지어 나누기

나중에 코드 목록과 이름 목록처럼 여러 문자열을 같은 순번끼리 짝지어 행으로 만들 일이 생겨, 원래는 입력을 15개까지 받는 함수(V2)를 만들었다. 지금 다시 보면 같은 LEVEL 로 각 문자열에서 같은 자리를 꺼내면 SQL 하나로 된다.

SELECT LEVEL AS RN
     , REGEXP_SUBSTR(:CODES, '([^,]*)(,|$)', 1, LEVEL, NULL, 1) AS CODE
     , REGEXP_SUBSTR(:NAMES, '([^,]*)(,|$)', 1, LEVEL, NULL, 1) AS NAME
  FROM DUAL
CONNECT BY LEVEL <= GREATEST(NVL(REGEXP_COUNT(:CODES, ','), 0),
                             NVL(REGEXP_COUNT(:NAMES, ','), 0)) + 1;

:CODES = 'A01,A02,A03', :NAMES = '사과,,포도' 이면 (A01, 사과), (A02, null), (A03, 포도) 가 나온다. 이렇게 자리를 맞춰야 하는 경우야말로 [^,]+ 패턴을 쓰면 안 되는 경우다. 원래 V2 는 이 패턴을 써서, 중간에 빈 값이 있으면 뒤 값들이 한 칸씩 당겨졌다.

정리

방법	버전	언제
CONNECT BY + REGEXP_SUBSTR	11g 이상	일회성, 문자열 하나
CROSS/OUTER APPLY + 위 방법	12c 이상	테이블 각 행 나누기
JSON_TABLE	12c 이상	값에 "·\ 가 없을 때, 순번 필요할 때
파이프라인 테이블 함수	전 버전	여러 곳에서 재사용, 구분자 자유
SQL 매크로	19c(19.7 이상)·21c 이상	재사용하면서 순수 SQL 로

다시 쓰며 바로잡은 점

• [^,]+ 패턴은 빈 항목을 건너뛰어 자리가 밀린다. ([^,]*)(,|$) 로 바꾸고, 함수 안은 INSTR·SUBSTR 로 다시 짰다.
• 구분자를 정규식 문자 클래스에 그대로 넣으면 |, ], - 같은 문자나 두 글자 이상 구분자에서 깨지던 문제를 없앴다.
• 파이프라인 함수 끝에 RETURN; 을 넣고, 행 타입에 순번(RN)을 더했다.
• 함수 없이 쓰는 방법(APPLY, JSON_TABLE, SQL 매크로)과 테이블 함수의 행 수 추정 문제를 덧붙였다.
• 15개 입력을 받던 V2 함수는 같은 일을 하는 SQL 한 문장으로 바꿨다.