[ORACLE] REGEXP_SUBSTR 을 이용하여 split 처럼 구분자로 자르기 [오라클]

REGEXP_SUBSTR 을 활용하여 SPLIT 효과를 내봅시다.

REGEXP_SUBSTR SPLIT

REGEXP_SUBSTR 함수호출 매개변수는
1. SOURCE STRING : 대상문자
2. PATTERN : 식 '[^,]+' 은 ,(대괄호 안의 쉼표 문자) 문자가 아닌(대괄호 안의 ^기호) 하나이상(+기호)의 어떤 값을 의미합니다.
3. POSITION : 시작위치
4. OCCURENCE : 몇 번째 발생인지?
4개를 사용하였습니다.

SPLIT 효과를 사용하는 간단한 예제는 다음과 같아요!

WITH TABLE_NAME AS (SELECT 'red,green,blue' AS COLUMN_NAME FROM DUAL)
SELECT REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 1) AS COLUMN_NAME_1,
       REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 2) AS COLUMN_NAME_2,
       REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 3) AS COLUMN_NAME_3
  FROM TABLE_NAME;

COLUMN_NAME_1 COLUMN_NAME_2 COLUMN_NAME_3
------------- ------------- -------------
red           green         blue

REGEXP_SUBSTR 함수를 다음과 같이 이중으로 사용해 보았습니다. 참고로 다음 예제는 정규표현식으로 가공해 보아도 좋아요~

WITH TABLE_NAME AS ( SELECT ' alpha = 5  ,red=     110,   green=5  , blue   =255' AS COLUMN_NAME FROM DUAL )
SELECT
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 1), '[^=]+', 1, 1)) AS COLUMN_NAME_1,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 1), '[^=]+', 1, 2)) AS COLUMN_NAME_2,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 2), '[^=]+', 1, 1)) AS COLUMN_NAME_3,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 2), '[^=]+', 1, 2)) AS COLUMN_NAME_4,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 3), '[^=]+', 1, 1)) AS COLUMN_NAME_5,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 3), '[^=]+', 1, 2)) AS COLUMN_NAME_6,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 4), '[^=]+', 1, 1)) AS COLUMN_NAME_7,
 TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^,]+', 1, 4), '[^=]+', 1, 2)) AS COLUMN_NAME_8
FROM TABLE_NAME;

COLUMN_NAME_1 COLUMN_NAME_2 COLUMN_NAME_3 COLUMN_NAME_4 COLUMN_NAME_5 COLUMN_NAME_6 COLUMN_NAME_7 COLUMN_NAME_8
------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
alpha         5             red           110           green         5             blue          255

Oracle Split

오라클 스플릿과 REGEXP_SUBSTR 함수 호출 매개변수(oracle 11g)에 대해 추가적으로 알아보는 시간입니다!

오라클은 SPLIT 함수를 명시적으로 제공하지는 않습니다.

그리하여 REGEXP_SUBSTR 함수를 사용해 비슷한 효과를 내보겠습니다.

  WITH TABLE_NAME AS (SELECT 'ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN' AS COLUMN_NAME FROM DUAL)
SELECT REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 1) AS COLUMN_NAME_1,
       REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 2) AS COLUMN_NAME_2,
       REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 3) AS COLUMN_NAME_3,
       REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 4) AS COLUMN_NAME_4,
       REGEXP_SUBSTR (COLUMN_NAME, '[^,]+', 1, 5) AS COLUMN_NAME_5
  FROM TABLE_NAME;

COLUMN_NAME_1  COLUMN_NAME_2 COLUMN_NAME_3  COLUMN_NAME_4 COLUMN_NAME_5
-------------  ------------- -------------  ------------- -------------
ANALYST        CLERK         MANAGER        PRESIDENT     SALESMAN

REGEXP_SUBSTR 함수 호출 매개변수(oracle 11g)

source_char : 대상 문자
=> 'ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN'
pattern : 표현식(Regular Expression)
=> '[^,]+'
=> '[^,]+' 은 쉼표(,)가 아닌(^) 하나 이상(+)의 어떤 값을 의미합니다.
position : 대상 문자에서 검색을 시작할 위치(기본값 1)
=> 1
occurrence : 표현식의 몇 번째 발생을 목표로 하는지 선택(기본값 1)
=> 1이면 첫 번째, 2면 두 번째 구분 값을 가져옵니다.

(참고) 5번, 6번 매개변수는 사용하지 않았습니다. 해당 부분은 이러한 매개변수 값도 있구나 하고 넘어가시면 됩니다.
5. match_parameter : 표현식 매개변수입니다. 예를 들면 'i'를 입력하면 대소문자를 구분하지 않는(case-insensitive) 매칭을 진행합니다.
6. subexpression : 기본값 0입니다. 0~9를 입력하실 수 있으며 매칭 표현에서 해당 순서에 해당하는 값을 반환합니다. (좌측 소괄호 매칭 기준)

SELECT
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) AS COLUMN_NAME_0,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) AS COLUMN_NAME_1,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 2) AS COLUMN_NAME_2,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) AS COLUMN_NAME_3,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) AS COLUMN_NAME_4,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 5) AS COLUMN_NAME_5,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 6) AS COLUMN_NAME_6,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 7) AS COLUMN_NAME_7,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 8) AS COLUMN_NAME_8,
        REGEXP_SUBSTR ('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 9) AS COLUMN_NAME_9
  FROM DUAL;

COLUMN_NAME_0 COLUMN_NAME_1 COLUMN_NAME_2 COLUMN_NAME_3 COLUMN_NAME_4 COLUMN_NAME_5 COLUMN_NAME_6 COLUMN_NAME_7 COLUMN_NAME_8 COLUMN_NAME_9
------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
12345678      123           45678         56            78            (NULL)        (NULL)        (NULL)        (NULL)        (NULL)

좌 괄호 순서대로 (123) (45678) (56) (78) 이 보이시죠?