[SQL] Oracle 구분자 행, 열, 분리, 자르기(REGEXP_SUBSTR, REGEXP_COUNT)

보통 타 시스템과 연계해서 데이터를 받으면 파이프 라인(|), 콤마(,) 등등 특수 문자를 활용해서 받게 되는 경우가 많을 것입니다. 그럴 때 각 항목을 파싱 하고, 적절한 패턴으로 조회하는 방법을 해보겠습니다.

구분자는 파이프 라인(|)을 기준으로 설명 할 예정이며, 아래 쿼리에서 적절하게 변경하면 다른 특수 문자도 충분히 가능합니다.

1. 데이터 정보

  IN_DATA 
    - TYPE  : VARCHAR2
    - VALUE : '|A||C| DEF |      G|||'

2. 파이프 라인으로 구분된 문자열 행(Column) 처리

/*
  - REPLACE 함수를 이용해서 '|' 부분자가 겹쳐 있는 항목을 띄워 놓기 위해서 '| ' 명시적으로 한 칸 띄웠습니다.
  - REGEXP_SUBSTR 정규 표현식 함수를 활용하면 행(Column)으로 나열 할 수 있습니다.
  - TRIM 함수를 이용해서 ' '빈 칸을 제거 합니다.
*/
SELECT    A.TEXT_DATA
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 1, 'i')) AS DATA_01
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 2, 'i')) AS DATA_02
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 3, 'i')) AS DATA_03
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 4, 'i')) AS DATA_04
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 5, 'i')) AS DATA_05
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 6, 'i')) AS DATA_06
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 7, 'i')) AS DATA_07
         ,TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, 8, 'i')) AS DATA_08
FROM      (SELECT    :IN_DATA AS TEXT_DATA, REGEXP_COUNT(:IN_DATA, '\|') AS C1_1
           FROM      DUAL) A
;

3. 파이프 라인으로 구분된 문자열 열(ROW)로 처리

/*
  - REPLACE 함수를 이용해서 '|' 부분자가 겹쳐 있는 항목을 띄워놓기 위해서 '| ' 명시적으로 한칸 띄웠습니다.
  - REGEXP_SUBSTR 정규 표현식 함수를 활용하면 열(ROW)로 나열 할 수 있습니다.
  - TRIM 함수를 이용해서 ' '빈 칸을 제거 합니다.
  - REGEXP_COUNT 정규표현식 함수를 사용해서 '|' 구분자의 수를 구합니다.
*/
SELECT    A.TEXT_DATA, A.TEXT_GB_CNT, TRIM(REGEXP_SUBSTR(REPLACE(TEXT_DATA, '|', '| '), '[^|]+', 1, B.NO, 'i')) AS DATA
FROM      (SELECT    :IN_DATA AS TEXT_DATA, REGEXP_COUNT(TO_CHAR(:IN_DATA), '\|') AS TEXT_GB_CNT
           FROM      DUAL) A
         ,COPY_T B
WHERE     B.NO <= A.TEXT_GB_CNT
;