펌) ORACLE Regular Expression ( 정규식 )

Regular Expression ( 정규식 )

문자열 데이터의 간단한 패턴 및 복잡한 패턴을 검색 할 수 있는 정규식은 기존의 LIKE 연산의 한계를 뛰어 넘는 막강한 검색 도구이다. 다양한 Meta Character 를 이용하여 복잡한 프로그래밍을 간단하게 해결 할 수 있으며 Data Validation, ETL ( Extract , Transform, Load ), Data Cleansing, Data Mining 등의 작업에서 유용하게 사용 될 수 있다. 제약조건으로 테이블의 Data 의 유효성을 검증 할 때도 사용 가능하다.

Oracle 10g Database 부터 추가 된 Function 을 이용한다.

LIKE 연산에서 간단한 패턴만 비교하던 것에 비해 정규식 패턴을 이용할 수 있다.

실습용 테이블을 생성하여 결과를 확인 해 보자.

SQL> @regexp_tab.sql

SQL> SELECT * FROM t1 ;

SQL> SELECT fname, lname

FROM t1

WHERE REGEXP_LIKE (fname, '^Ste(v|ph)en$') ;

LIKE 연산은 '_' ,'%' 의 wildcard 를 이용하여 패턴을 비교 하지만 정규식에서는 Meta Character 를 이용한다. 위의 정규식 패턴을 분석 하면 다음과 같다.

즉, 'Steven' 또는 'Stephen' 의 문자열을 검색 할 수 있게 된다. 이렇게 정규식 패턴을 사용하려면 Meta Character 의 종류 및 사용법을 확인해야 한다.

Meta Character

이러한 Meta Character 를 이용하여 정규식의 사용 방법을 확인 해 보자.

REGEXP_LIKE

출처 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions007.htm#SQLRF00501

LIKE 연산과 같이 WHERE 절에서 사용하며 정규식에 대한 패턴을 비교할 수 있다.

<v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f><o:lock aspectratio="t" v:ext="edit"></o:lock>

SQL> SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '...\...\.....\.......') ;

검색 결과 중 문자 ABC 가 포함 된 것도 함께 검색 된다. 임의의 한 문자를 비교 하기 때문에 어쩔 수 없는 상황이다. 다음의 문장을 보자.

SQL> SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '[0-9]{3}\.[0-9]{2}\.[0-9]{4}\.[0-9]{6}') ;

또는 WHERE REGEXP_LIKE (phone, '\d{3}\.\d{2}\.\d{4}\.\d{6}') ;

각각의 자리마다 반복 되는 회수를 지정할 수 있으며 원하는 문자열이 포함 된 것을 찾을 수 있다.

REGEXP_REPLACE

출처 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions130.htm#SQLRF06302

정규식에 의한 패턴을 찾아 대체 문자열로 변경을 할 수 있다.

SQL> SELECT fname, phone, REGEXP_REPLACE ( phone , '\.' , '-' ) new_format

FROM t1 ;

"." 으로 구분 된 문자를 "-" 으로 변경

SQL> SELECT fname, phone,

REGEXP_REPLACE (phone, '(\d{3})\.(\d{3})\.(\d{4})','(\1)-\2-\3') new_phone

FROM t1 ;

단순한 대체 문자열 지정에서 정규식을 이용한 복잡한 문자열 대체까지 여러 Meta Character 를 활용하여 작업 할 수 있다.

REGEXP_INSTR

출처 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions129.htm#SQLRF06300

정규식 패턴 비교를 통해 위치 값을 확인 한다.

SQL> SELECT fname, addr,

REGEXP_INSTR ( addr, '[[:alpha:]]' ) pos ,

phone,

REGEXP_INSTR ( phone, '[[:alpha:]]') pos

FROM t1 ;

지정된 Class 가 알파벳을 찾는 부분이므로 첫 번째 알파벳 문자의 위치를 검색 한다.

REGEXP_SUBSTR

출처 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions131.htm#SQLRF06303

정규식 패턴을 검색하여 부분 문자를 추출 한다.

SQL> SELECT fname, addr,

REGEXP_SUBSTR ( addr, ' [^ ]+ ') road

FROM t1 ;

정규식을 사용하여 두 번째 문자열 (Road) 추출

SQL> SELECT fname, phone,

REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code

FROM t1 ;

지역번호를 뺀 국번만 추출 가능 ( 빈 공백 등으로 구분되지 않은 경우에도 가능 )

Oracle Database 11g : New Features

REGEXP_COUNT

출처 : http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions145.htm#SQLRF20014

사용 가능하며 정규식 패턴을 검색하여 발견 된 횟수를 계산 한다.

SQL> SELECT fname, addr,

REGEXP_COUNT(addr,'a') cnt

FROM t1 ;

'a' 가 발견 된 횟수 검색 가능

※ 동일한 결과는 다음의 문장으로도 해결 가능 하다. ( v10g 이하 )

SQL> SELECT fname, addr,

LENGTH(addr) - LENGTH(REPLACE(addr, 'a')) cnt

FROM t1 ;

ADDR 컬럼에서 a 문자를 null 로 대체 시키고 전체 길이에서 차감 한다.

Subexpressions ( 하위식 )

하위식은 REGEXP_INSTR, REGEXP_SUBSTR 에서 지원되며 정규식의 검색을 진행 할 때 특정 문자열을 지정할 수 있다.

SQL> SELECT REGEXP_INSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 0, 'i', 2 ) "Position"

FROM dual ;

위의 예제는 12345678 의 문자 패턴을 비교하면서 45678 의 문자열이 시작되는 위치를 찾아 준다.

SQL> SELECT REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 1 ) "Exp1" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 2 ) "Exp2" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 3 ) "Exp3" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 4 ) "Exp4"

FROM dual ;

12345678 의 문자열을 검색하며 ( ) 의 순서에 따라 하위식 순번을 결정 한다. 첫 번째 하위식은 (123) 이며 두 번째 하위식은 (45678) 이 된다. 세 번째, 네 번째 하위식은 각각 (56), (78) 이 해당 된다. REGEXP_INSTR 은 위치를 찾을 수 있으며 REGEXP_SUBSTR 은 그 문자열에 해당 되는 부분을 추출 할 수 있다.

이런 하위식은 생명 과학 분야의 DNA Sequencing 에서 사용 될 수 있다.

SQL> SELECT REGEXP_INSTR('ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc

taccccagagcacttagagccag', '(gtc(tcac)(aaag))', 1, 1, 0, 'i', 2) "Position"

FROM dual;

단순히 tcac 의 문자만을 비교 하는 것이 아니며, "gtctcacaaag" 의 문자열이 함께 있을 때 tcac 의 위치를 반환해 줄 수 있다.

또는 앞의 예문 중에 다음의 경우도 사용 가능 하다.

SQL> SELECT fname, phone,

REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code

FROM t1 ;

국번에 해당되는 문자열을 추출 하고 있으나 앞뒤로 붙어 있는 "." 문자를 제거 하기 위해 REPLACE 를 함께 사용 하고 있다. 하위식을 사용하면 다음과 같이 실행 가능하다.

SQL> SELECT fname, phone,

REGEXP_SUBSTR(phone, '(\.)(\d{3})(\.)',1,1,'i',2) code

FROM t1 ;