오라클 정규식 (REGEXP) 사용법

 

오라클 정규식 (REGEXP)

오라클은 10g 부터 REGEXP로 시작하는 함수를 지원 합니다. (Regular Expression 이라는 정규식의 의미 입니다.)
이 함수를 통해 데이터의 패턴을 보다 다양하게 찾고, 변경할 수 있게 되었습니다.

정규식 함수는 다음과 같습니다.

함수	설명
REGEXP_LIKE	Like 연산과 유사하여 정규식 패턴을 검색
REGEXP_REPLACE	정규식 패턴을 검색하여 대체 문자열로 변경
REGEXP_INSTR	정규식 패턴을 검색하여 위치 반환
REGEXP_SUBSTR	정규식 패턴을 검색하여 부분 문자 추출
REGEXP_COUNT(v11g)	정규식 패턴을 검색하여 발견된 횟수를 반환

그리고 정규식에는 메타문자와 리터럴 문자라는 것이 있습니다.

※ 메타문자란? 
   검색 알고리즘을 지정하는 연산자를 뜻합니다.

※ 리터럴 문자란?
   검색 중인 일반적인 문자를 뜻합니다.

메타문자들은 이렇습니다.

메타문자	설명
.	임의의 한 문자
?	앞 문자가 없거나 하나 있음 (0 또는 1번 발생)
+	앞 문자가 하나 이상 있음
*	앞 문자가 0개 이상 있음
{m}	선행 표현식의 정확히 m 번 발생
{m,}	선행 표현식이 최소 m번 이상 발생
{m,n}	선행 표션식이 최소 m번 이상 , 최대 n 번 이하 발생
[...]	괄호 안의 리스트에 있는 임의의 단일 문자와 일치
|	OR 를 나타 냄
(...)	괄호로 묵인 표현식을 한 단위로 최금
^	문자열 시작 부분과 일치
[^]	해당 문자에 해당하지 않는 한 문자
$	문자열의 끝 부분과 일치
\	표현식에서 후속 문자를 리터럴로(일반 문자) 처리
\n	괄호 안에 그룹화 된 n 번째(1-9) 선행 하위식과 일치
\d	숫자 문자
[:class:]	지정된 POSIX 문자 클래스에 속한 임의의 문자와 일치 [:alpha:] 알파벳문자 [:digit:] 숫자 [:lower:] 소문자 알파벳 문자 [:upper:] 대문자 알파벳 문자 [:alnum:] 알파벳/숫자 [:space:] 공백 문자 [:punct:] 특수문자 [:cntrl:] 컨트롤 문자 [:print:] 출력 가능한 문자
[^:class:]	괄호안의 리스트에 없는 임의의 단일분자와 일치

 

REGEXP_LIKE

LIKE를 보시면 예상하셨겠지만 표현 패턴에 포함되는 경우 값을 반환하게 됩니다.

REGEXP_LIKE(srcstr, pattern[,match_option])
 
- srcstr
  소스문자열, 검색하고자 하는 값
- pattern
  Regular Expression Operator를 통해 문자열에서 특정 문자를 보다 다양한 pattern으로 검색하는 것이 가능

- match_option
  match를 시도할 때의 옵션. 찾고자 하는 문자의 대소문자 구분이 기본으로 설정. 대소문자를 구분할 필요가
  없다면 'i' 옵션 사용을 지정한다

- [ ] : [] 안에 명시되는 하나의 문자라도 일치하는 것이 있으면 나타냄

 

REGEXP_REPLACE

REGEXP_REPLACE는 정규식패턴에 해당하는 문자에 대해 변환 해주는 함수 입니다.

REGEXP_REPLACE(source_char, pattern[,replace_string[,position[,occurrence[,math_param]]]]

- source_char : 원본데이터나 컬럼

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

- replace_string : 변환하고자 하는 형태

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

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

 - match_parameter  : 기본값으로 검색되는 옵션을 바꿀 수 있습니다.

 

REGEXP_INSTR

특정패턴의 데이터가 나오는 첫번째 위치를 나타냅니다.

REGEXP_INSTR(source_char, pattern[,position][,occurrence][,return oprion][,match option])


- source_char : 원본데이터나 컬럼

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

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

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

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

- match option : c : 대소문자를 구분(기본값)
                       i : 대소문자를 구분하지 않음

 

REGEXP_SUBSTR

특정패턴을 찾아서 잘라주는 역할을 합니다.

REGEXP_SUBSTR(source_char, pattern[,position][,occurrence][,match option])


- source_char : 원본데이터나 컬럼

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

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

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

- match option : c : 대소문자를 구분(기본값)
                       i : 대소문자를 구분하지 않음

 

REGEXP_COUNT

특정 문자나 문자열의 개수를 세는 함수 입니다.

REGEXP_COUNT(source_char, pattern[,position][,occurrence][,match option])

- source_char : 원본데이터나 컬럼

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

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

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

- match option : c : 대소문자를 구분(기본값)
                       i : 대소문자를 구분하지 않음

---

실전예제

그럼 임의로 데이터를 만들어 놓고 예제를 돌리겠습니다.

CREATE TABLE REG_TEST
(
  NUM NUMBER(10)
  , DATA VARCHAR2(100)
);

INSERT INTO REG_TEST(NUM, DATA)VALUES(1,'ABCDEFG');
INSERT INTO REG_TEST(NUM, DATA)VALUES(2,'12314215');
INSERT INTO REG_TEST(NUM, DATA)VALUES(3,'SDF123');
INSERT INTO REG_TEST(NUM, DATA)VALUES(4,'sdfwo12345');
INSERT INTO REG_TEST(NUM, DATA)VALUES(5,'!@#%#$$%&%$&');
INSERT INTO REG_TEST(NUM, DATA)VALUES(6,'010-1234-5678');
INSERT INTO REG_TEST(NUM, DATA)VALUES(7,'23425');
INSERT INTO REG_TEST(NUM, DATA)VALUES(8,'sdfo@#$%d123');
INSERT INTO REG_TEST(NUM, DATA)VALUES(9,'7812011184563');
INSERT INTO REG_TEST(NUM, DATA)VALUES(10,'teste@gmail.com');
INSERT INTO REG_TEST(NUM, DATA)VALUES(11,'가나다라마바사');
INSERT INTO REG_TEST(NUM, DATA)VALUES(12,'hrogrteojo');

 

숫자가 포함된 데이터 찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '[0-9]')
 ;

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '[[:digit:]]')
 ;

 

숫자만 있는 데이터 찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_REPLACE(A.DATA, '[0-9]') IS NULL

 

영문 + 숫자로만 되어있는 데이터 찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_REPLACE(REGEXP_REPLACE(A.DATA, '[0-9]'),'[a-zA-Z]') IS NULL
   AND REGEXP_LIKE(A.DATA, '[0-9]')
   AND REGEXP_LIKE(A.DATA, '[a-zA-Z]')

 

영문으로만 되어있는 데이터 찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_REPLACE(A.DATA,'[a-zA-Z]') IS NULL
;

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '^[[:alpha:]]*$')

 

한글로만 되어있는 데이터  찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '^[가-힝]*$');

 

특수문자만 되어있는 데이터 찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_REPLACE(A.DATA,'[[:punct:]]') IS NULL
;

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '^[[:punct:]]*$')

 

전화번호 패턴 찾기

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '0[0-9]{1,2}-[0-9]{3,4}-[0-9]{4}')

 

이메일 패턴 찾아서 도메인 뽑아내기

SELECT A.*
     , LTRIM(REGEXP_SUBSTR(A.DATA,'@([[:alnum:]]+\.?){3,4}'),'@') AS DOMAIN
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '@([[:alnum:]]+\.?){3,4}')

 

문자만 있는 데이터 추출

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_REPLACE(A.DATA, '[^0-9]') IS NULL
   AND NOT REGEXP_LIKE(A.DATA, '[[:punct:]]')

 

A, B, C가 순서대로 있는 모든 문자열

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, 'a.*B.*C','i') /* i는 대소문자를 구분하지 않겠다는 것 */

 

A.B 또는 공백이 있는 모든 문자열

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, '[ab ]','i')

 

알파벳 o 뒤에 숫자 1이 오는 모든 문자열

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, 'o[1]')

 

알파벳 o 뒤에 1이 오지않는 모든 문자열

SELECT A.*
  FROM REG_TEST A
 WHERE REGEXP_LIKE(A.DATA, 'o[^1]')

 

문자열 'ABC' 의 개수

SELECT regexp_count(A.DATA , 'ABC') AS CNT
     , A.*
  FROM REG_TEST A

 

 

끝으로...

쿼리를 짤때 정규식을 잘 활용하면 굉장히 짧게 쿼리를 짤 수 있어요.

자주 나오는 패턴의 정규식은 암기하여 바로바로 써 먹을 수 있도록 합시당~