[ORACLE DB] 오라클에서 정규표현식 Regexp 함수 사용 방법

오라클 SQL Query에서 검색과 치환 등에 정규식을 사용 가능하도록 오라클 10g부터 제공되는 함수를 사용하는 방법을 알아보도록 하겠습니다.

 

먼저 정규식으로 사용가능한 함수는 아래와 같습니다.

 

REGEXP_REPLACE

REGEXP_SUBSTR

REGEXP_INSTR

REGEXP_LIKE

 

ORACLE DB에서는 위의 4가지 함수가 제공됩니다.

 

1. REGEXP_REPLACE

Syntex : regexp_replace(원본 문자열, Pattern [, 대체문자열 [, 시작위치 [, 일치횟수 [, 매칭 매개변수 ]]]] );

- 원본 문자열 : 원본데이터나 컬럼
- pattern : 찾고자 하는 데이터 패턴
- 대체문자열 : 변환하고자 하는 형태
- 시작위치 : 검색시작위치를 지정(기본값은 1)
- 일치횟수 : 패턴과 일치가 발생하는 횟수
  (0은 모든값을 대체하고 다른 n이란 숫자는 n번째 발생하는 문자열을 대입함)
- 매칭 매개변수  : 기본값으로 검색되는 옵션을 바꿀 수 있습니다.

 

예제 ) 'Anderson' 에서 a, e, i, o, u를 첫 번째 자리부터 모두 찾아 'G'로 변경합니다. 대소문자를 구별하지 않습니다.

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G', 1, 0, 'i')
FROM dual;

Result: 'GndGrsGn'

 

 

REGEXP_SUBSTR

Syntax : REGEXP_SUBSTR( 원본 문자열, 
        pattern [, 검색 시작위치 [, 발생 횟수 [, 매칭 파라미터 ] ] ] )

- 원본 문자열 : 원본데이터나 컬럼

- pattern : 찾고자 하는 데이터 패턴

- 검색 시작 위치 : 검색시작위치를 지정(기본값은 1)

- 발생 횟수 : 패턴과 일치가 발생하는 횟수
             (0은 모든값을 대체하고 다른 n이란 숫자는 n번째 발생하는 문자열을 대입함)

- 매칭 파라미터  : c : 대소문자를 구분(기본값)
                   i : 대소문자를 구분하지 않음

 

예제) last_name에서 첫 번째 모음(a,e,i,o,u)이 나타나면 반환합니다. 대소문자를 구분하지 않습니다.

SELECT REGEXP_SUBSTR (last_name, 'a|e|i|o|u', 1, 1, 'i') AS "First Vowel"
FROM contacts;

 

REGEXP_INSTR

Syntax : regexp_instr(원본 문자열, 
Pattern [, 검색 시작 위치 [, 발생 횟수 [, 반환 옵션 [, Match를 시도할 때의 옵션]]]]);

- 원본 문자열 : 원본데이터나 컬럼

- pattern : 찾고자 하는 데이터 패턴

- 검색 시작 위치 : 검색시작위치를 지정(기본값은 1)

- 발생 횟수 : 패턴과 일치가 발생하는 횟수
             (0은 모든값을 대체하고 다른 n이란 숫자는 n번째 발생하는 문자열을 대입함)

- 반환 옵션 : 0 : 발생 값의 첫번째 위치를 반환(기본값)
              1 : 발생 값의 다음 문자 위치를 반환

- Match를 시도할 때의 옵션 : c : 대소문자를 구분(기본값)
                            i : 대소문자를 구분하지 않음

 

예제) B(b)또는 C(c)가 2번 이상 문자열에 들어 있는 데이터 검색

SELECT * FROM TEST_REGEX WHERE REGEXP_INSTR(VALUE, '[b-c]', 1, 2, 0, I)>0;

 

REGEXP_LIKE

Syntax : regexp_like(원본 문자열, Pattern [, Match를 시도할 때의 옵션]);

- 원본 문자열 : 원본데이터나 컬럼

- pattern : 찾고자 하는 데이터 패턴

- Match를 시도할 때의 옵션 : c : 대소문자를 구분(기본값)
                            i : 대소문자를 구분하지 않음

예제) okjsp로 시작하는 문장 모두 찾기

SELECT * FROM TEST_REGEX WHERE REGEXP_LIKE(VALUE, '^okjsp*');