[Oracle] 쉼표(,)로 구분된 문자열을 행(Row)으로 분리해 검색 조건 처리하기 (REGEXP_SUBSTR + CONNECT BY)

실무에서 프로시저, 레포트 툴, 또는 화면 검색 조건을 처리하다 보면 여러 개의 ID나 코드가 ITEM001,ITEM002,ITEM003처럼 콤마(,)로 구분된 단일 문자열 형태로 넘어오는 경우가 자주 있습니다.

보통 애플리케이션(Java/Spring)단에서 split(",") 후 MyBatis의 <foreach>를 태우는 것이 가장 일반적이지만, 순수 SQL 환경, 프로시저, 쿼리 바인딩 제약 등으로 인해 오라클 쿼리 내부에서 직접 문자열을 분리해야 할 때가 있습니다.

이번 글에서는 오라클의 정규표현식(REGEXP_SUBSTR)과 계층형 쿼리(CONNECT BY LEVEL)를 활용해 단일 문자열을 행(Row)으로 분리하고 검색 조건에 매핑하는 기법을 상세히 분석해 보겠습니다.

---

💡 요약

• 오라클에서 콤마로 구분된 문자열을 자를 때는 REGEXP_SUBSTR + `CONNECT BY LEVEL` 조합이 대표적인 패턴입니다.
• REGEXP_COUNT('&arg', ',') + 1을 통해 분리할 개수를 지정할 수 있습니다.
• 파라미터의 NULL 여부에 따른 분기 처리를 추가하면 동적 검색 조건으로 안전하게 사용할 수 있습니다.

---

1. 사용된 쿼리 및 전체 구조 분석

먼저 작성된 조건절 쿼리를 가독성 있게 정리한 구조입니다.

AND 'TRUE' = CASE 
    -- 1) 검색 파라미터가 없으면 조건 패스
    WHEN '&arg_goods' IS NULL THEN 'TRUE'

    -- 2) 파라미터가 있으면 콤마로 잘라낸 목록과 매칭 여부 검사
    WHEN EXISTS (
        SELECT 'X'
        FROM (
            SELECT DISTINCT REGEXP_SUBSTR(A.TXT, '[^,]+', 1, LEVEL) AS GOODS_CODE
            FROM (SELECT '&arg_goods' AS TXT FROM DUAL) A
            CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(A.TXT, '[^,]+', '')) + 1
        ) H
        WHERE H.GOODS_CODE = GD.GOODS_CODE
    ) THEN 'TRUE'

    ELSE 'FALSE' 
END

이 쿼리는 크게 3가지 핵심 동작으로 나뉩니다.

---

2. 쿼리 동작 원리 파헤치기

① 콤마 개수 세기 및 반복 횟수 계산

LENGTH(REGEXP_REPLACE(A.TXT, '[^,]+', '')) + 1

• REGEXP_REPLACE(A.TXT, '[^,]+', ''): 콤마(,)가 아닌 모든 문자를 제거하여 오직 콤마만 남깁니다.
• LENGTH(...): 남은 콤마의 개수를 계산합니다.
• + 1: 데이터의 개수는 (콤마 개수 + 1)개이므로 최종 분리할 아이템의 총 개수가 됩니다.
• (예: A,B,C → 콤마 2개 → 길이 2 + 1 = 3)*

② 문자열 행(Row) 분리

SELECT DISTINCT REGEXP_SUBSTR(A.TXT, '[^,]+', 1, LEVEL) AS GOODS_CODE
FROM (SELECT '&arg_goods' AS TXT FROM DUAL) A
CONNECT BY LEVEL <= (전체_아이템_수)

• CONNECT BY LEVEL <= N: 1부터 N까지의 행(Row)을 동적으로 생성합니다.
• REGEXP_SUBSTR(A.TXT, '[^,]+', 1, LEVEL):
• [^,]+: 콤마를 제외한 1개 이상의 문자 패턴
• LEVEL: LEVEL번째에 매칭되는 문자열을 추출합니다. (1번째 행은 1번째 값, 2번째 행은 2번째 값...)

• DISTINCT: 중복으로 입력된 코드가 있을 경우 중복 행을 제거합니다.

실행 결과 예시 ('A,B,C' 입력 시):

GOODS_CODE
A
B
C

③ NULL 처리 및 EXISTS 조건 결합

• 파라미터(&arg_goods)가 NULL로 들어오면 전체 조회를 위해 바로 'TRUE'를 반환합니다.
• 파라미터가 존재하면 인라인 뷰(H)에서 분리된 행들과 기준 테이블의 GD.GOODS_CODE를 비교하여 일치하는 데이터가 존재하는지(EXISTS) 확인합니다.

---

3. 실무 관점에서의 리팩토링 & 권장 팁

해당 쿼리는 단일 SQL 내에서 콤마 문자열을 깔끔하게 처리하는 좋은 방법이지만, 실무 적용 시 다음 사항들을 고려하면 더욱 좋습니다.

💡 1) CASE WHEN 대신 표준적인 WHERE 절 간소화

'TRUE' = CASE WHEN ... 형태는 쿼리가 다소 장황해질 수 있으므로 아래와 같이 직관적인 OR / IN 조건으로 단순화할 수 있습니다.

AND (
    -- 파라미터가 NULL이거나 비어있으면 조건 전체 패스
    '&arg_goods' IS NULL 
    OR GD.GOODS_CODE IN (
        SELECT DISTINCT REGEXP_SUBSTR('&arg_goods', '[^,]+', 1, LEVEL)
        FROM DUAL
        CONNECT BY LEVEL <= REGEXP_COUNT('&arg_goods', ',') + 1
    )
)

Tip (Oracle 11g 이상):
콤마 개수를 셀 때 복잡한 LENGTH(REGEXP_REPLACE(...)) 대신 REGEXP_COUNT(str, ',') 함수를 사용하면 훨씬 간결합니다.

💡 2) 성능(Performance) 주의점

• CONNECT BY LEVEL 방식은 입력된 아이템 수가 수십~수백 개 수준일 때는 매우 편리하고 빠릅니다.
• 하지만 수천 개 이상의 대량 문자열을 반복적으로 파싱해야 하거나, 인덱스를 타야 하는 대용량 테이블과 조인할 때는 CPU 부하가 발생할 수 있습니다.
• 대량 처리가 필요하다면 애플리케이션단에서 배열/리스트로 변환해 바인딩하거나, DB Global Temporary Table(GTT)을 사용하는 방안을 권장합니다.

---

 

 

2탄 # [Oracle] 콤마 문자열 파싱 4가지 방식 성능 및 실무 비교 (XMLTABLE vs JSON_TABLE vs CONNECT BY)