REGEXP_SUBSTR

유용하게 사용했던 오라클 정규식 함수 REGEXP_SUBSTR() 

REGEXP_SUBSTRSUBSTR 정규 표현식 패턴에 대한 문자열을 검색

REGEXP_INSTR와 비슷 하지만 하위 문자열의 위치를 ​​반환하는 대신 하위 문자열 자체를 반환

REGEXP_SUBSTR(source_string, pattern

              [, start_position

                 [, occurrence

                    [, match_parameter

                       [, subexpr

                       ]

                    ]

                 ]

              ]

             )

1) source_string : 검색 값으로 사용되는 문자식 (char, varchar2, clob 등등)

2) pattern : 정규 표현식 패턴 

3) start_position : 검색을 시작해야하는 문자의 자리수 (기본값은 1)

4) occurrence : 함수가 검색해야하는 검색 패턴 발생을 지정하는 정수 값 (기본값은 1)

5) match_parameter : 함수의 기본 일치 동작을 변경할 수 있는 텍스트 리터널

• 'i' 대 / 소문자를 구분하지 않는 일치를 지정
• 'c' 대소 문자를 구분하는 일치를 지정
• 'n' 마침표 (.) (임의의 문자와 일치하는 문자)가 줄 바꿈 문자와 임의의 모든 문자와 일치
  (매개 변수를 생략하면 마침표가 개행 문자를 제외한 모든 문자 일치)
  
  ex) match_parameter 'n' 없을경우
  
  

  1

  SELECT REGEXP_SUBSTR('hello'||CHR(10)||', to you hi', '.,',1,1) FROM DUAL

  cs

  
  결과 : o,
  
  ex) match_parameter 'n' 있을경우
  
  

  SELECT REGEXP_SUBSTR('hello'||CHR(10)||', to you hi', '.,',1,1,'n') FROM DUAL

  결과 : ...
  
  
• 'm'소스 문자열을 여러 줄로 취급 (이 매개 변수를 생략하면 Oracle은 소스 문자열을 단일 행으로 취급)

생략하면 다음과 같은 기능으로 작동

• 대소 문자 일치는 NLS_SORT 매개 변수에 의해 결정
• 마침표 (.)가 줄 바꿈 문자와 일치하지 않음
• 소스 문자열은 단일 행으로 처리
  

https://ko.wikipedia.org/wiki/%EC%A0%95%EA%B7%9C_%ED%91%9C%ED%98%84%EC%8B%9D

활용 예 )

체크박스로 된 검색 결과 값 조회 SELECT문 WHERE절 IN()안에 

- 체크된 value 값을 'value1,value2,value3' 쉼표단위로 하나의 문자열로 가져왔을 시

SELECT

...

.... AND TYPE IN (

SELECT TO_CHAR(TRIM(REGEXP_SUBSTR('1,2,3,4,10,a12', '[^,]+', 1, LEVEL))) AS TYPE_ID 
FROM DUAL
CONNECT BY INSTR('1,2,3,4,10,a12', ',', 1, LEVEL - 1) > 0

IN 안에 SELECT 문 결과 값 : 

TYPE_ID        

--------------

1             

2             

3             

4             

10            

a12 

다중 등록 문에서

INSERT ALL INTO 테이블 명(컬럼1, 컬럼2, 컬럼3)

SELECT '컬럼1 고정벨류값' ,
       '컬럼2 고정벨류값', 
        REGEXP_SUBSTR(RTRIM('a,b,c,ab,abc'),'[^,]+', 1,LEVEL) AS "컬럼3 유동벨류값"
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(RTRIM('a,b,c,ab,abc'),'[^,]+', 1,LEVEL) IS NOT NULL

SELECT문 결과값 : 

컬럼1고정벨류값'  '컬럼2고정벨류값'  컬럼3 유동벨류값        

----------             ----------            ------------

컬럼1 고정벨류값  컬럼2 고정벨류값  a           

컬럼1 고정벨류값  컬럼2 고정벨류값  b           

컬럼1 고정벨류값  컬럼2 고정벨류값  c           

컬럼1 고정벨류값  컬럼2 고정벨류값  ab          

컬럼1 고정벨류값  컬럼2 고정벨류값  abc