출처: [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
;