펌) ORACLE Regular Expression ( 정규식 )
Regular Expression ( 정규식 )
문자열 데이터의 간단한 패턴 및 복잡한 패턴을 검색 할 수 있는 정규식은 기존의 LIKE 연산의 한계를 뛰어 넘는 막강한 검색 도구이다. 다양한 Meta Character 를 이용하여 복잡한 프로그래밍을 간단하게 해결 할 수 있으며 Data Validation, ETL ( Extract , Transform, Load ), Data Cleansing, Data Mining 등의 작업에서 유용하게 사용 될 수 있다. 제약조건으로 테이블의 Data 의 유효성을 검증 할 때도 사용 가능하다.
Oracle 10g Database 부터 추가 된 Function 을 이용한다.
Function | Description |
REGEXP_LIKE | Like 연산과 유사하며 정규식 패턴을 검색 |
REGEXP_REPLACE | 정규식 패턴을 검색하여 대체 문자열로 변경 |
REGEXP_INSTR | 정규식 패턴을 검색하여 위치 반환 |
REGEXP_SUBSTR | 정규식 패턴을 검색하여 부분 문자 추출 |
REGEXP_COUNT ( v11g ) | 정규식 패턴을 검색하여 발견된 횟수 반환 |
LIKE 연산에서 간단한 패턴만 비교하던 것에 비해 정규식 패턴을 이용할 수 있다.
실습용 테이블을 생성하여 결과를 확인 해 보자.
SQL> @regexp_tab.sql
SQL> SELECT * FROM t1 ;
EMPNO FNAME LNAME PHONE ADDR ---------- ---------- ---------- -------------------- ---------------------------------------- 200 Jennifer Whalen 515.123.4444 2004 Charade Rd 201 Michael Hartstein 515.123.5555 147 Spadina Ave 114 Den Raphaely 515.127.4561 2004 Charade Rd 203 Susan Mavris 515.123.7777 8204 Arthur St 137 Renske Ladwig 650.121.1234 2011 Interiors Blvd 106 Valli Pataballa 590.423.4560 2014 Jabberwocky Rd 204 Stephen Baer 010.45.1343.329ABC Schwanthalerstr. 7031 173 Sundita Kumar 011.44.1343.329268 Magdalen Centre, The Oxford Science Park 100 Steven King 515.123.4567 2004 Charade Rd 109 Daniel Faviet 515.124.4169 2004 Charade Rd 205 Shelley Higgins 515.123.8080 2004 Charade Rd |
SQL> SELECT fname, lname
FROM t1
WHERE REGEXP_LIKE (fname, '^Ste(v|ph)en$') ;
FNAME LNAME ---------- ---------- Stephen Baer Steven King |
LIKE 연산은 '_' ,'%' 의 wildcard 를 이용하여 패턴을 비교 하지만 정규식에서는 Meta Character 를 이용한다. 위의 정규식 패턴을 분석 하면 다음과 같다.
^ : 문자열 시작 부분 일치 ( 문자열의 시작이 Ste 인 부분 검색 ) (v|ph) : 리터럴 문자 v 또는 ph 검색 $ : 문자열 끝부분 일치 ( 문자열의 끝이 en 인 부분 검색 ) |
즉, 'Steven' 또는 'Stephen' 의 문자열을 검색 할 수 있게 된다. 이렇게 정규식 패턴을 사용하려면 Meta Character 의 종류 및 사용법을 확인해야 한다.
Meta Character
Meta Character | Description |
. | 지원되는 Character set 에서 NULL 을 제외한 임의의 문자와 일치 |
+ | 한 번 이상 발생 수 일치 |
? | 0 또는 1번 발생 수 일치 |
* | 선행 하위식의 0번 이상 발생 수 일치 |
{m} | 선행 표현식의 정확히 m번 발생 수 일치 |
{m , } | 선행 하위식과 최소 m번 이상 발생 수 일치 |
{m , n } | 선행 하위식의 최소 m번 이상, 최대 n번 이하 발생 수 일치 |
[ ... ] | 괄호 안의 리스트에 있는 임의의 단일 문자와 일치 |
| | 여러 대안 중 하나와 일치 ( OR ) |
( . . . ) | 괄호로 묶인 표현식을 한 단위로 취급함. 하위식은 리터럴의 문자열이나 연산자를 포함한 복잡한 표현식 가능 |
^ | 문자열 시작 부분과 일치 |
$ | 문자열 끝 부분과 일치 |
\ | 표현식에서 후속 메타 문자를 리터럴로 처리 (ESCAPE) |
\n | 괄호 안의 그룹화된 n번째 (1~9) 선행 하위식과 일치. 괄호는 표현식이 기억되도록 만들고 backreference 에서 표현식 참조 |
\d | 숫자 문자 |
[ :class: ] | 지정된 POSIX 문자 클래스에 속한 임의의 문자와 일치 [:alpha:] 알파벳 문자 [:digit:] 숫자 [:lower:] 소문자 알파벳 문자 [:upper:] 대문자 알파벳 문자 [:alnum:] 알파벳/숫자 [:space:] 공백 문자 [:punct:] 구두점 기호 [:cntrl:] 컨트롤 문자 [:print:] 출력 가능한 문자 |
[^:class:] | 괄호 안의 리스트에 없는 임의의 단일 문자와 일치 |
이러한 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, '...\...\.....\.......') ;
FNAME PHONE ---------- -------------------- Stephen 010.45.1343.329ABC Sundita 011.44.1343.329268 |
. (period) : 임의의 한 문자 \. : \ 뒤에 나오는 . 은 Meta Character 가 아닌 리터럴 문자 ( ESCAPE ) 즉, ???.??.????.?????? 의 패턴으로 저장된 전화 번호를 검색 함. ( ? 는 임의의 문자 ) |
검색 결과 중 문자 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}') ;
FNAME PHONE ---------- -------------------- Sundita 011.44.1343.329268 |
[0-9] : 숫자 0 에서 9 사이의 값 (범위 지정 가능) \d : 숫자 문자 {3} : 3번 반복 |
각각의 자리마다 반복 되는 회수를 지정할 수 있으며 원하는 문자열이 포함 된 것을 찾을 수 있다.
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 ;
FNAME PHONE NEW_FORMAT ---------- -------------------- -------------------- Jennifer 515.123.4444 515-123-4444 Michael 515.123.5555 515-123-5555 Den 515.127.4561 515-127-4561 ... |
"." 으로 구분 된 문자를 "-" 으로 변경
SQL> SELECT fname, phone,
REGEXP_REPLACE (phone, '(\d{3})\.(\d{3})\.(\d{4})','(\1)-\2-\3') new_phone
FROM t1 ;
FNAME PHONE NEW_PHONE ---------- -------------------- -------------------- Jennifer 515.123.4444 (515)-123-4444 Michael 515.123.5555 (515)-123-5555 Den 515.127.4561 (515)-127-4561 Susan 515.123.7777 (515)-123-7777 ... |
(\d{3})\.(\d{3})\.(\d{4}) : 3 자리로 표현 되는 전화 번호 검색 ( 구분자 "." 사용 ) (\1)-\2-\3 : 3개의 그룹 문자를 표현하며 1번 그룹은 ( ) 로 감싸고 구분자는 "-" 사용 |
단순한 대체 문자열 지정에서 정규식을 이용한 복잡한 문자열 대체까지 여러 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 ;
FNAME ADDR POS PHONE POS ---------- ------------------------- ---------- -------------------- ---------- Jennifer 2004 Charade Rd 6 515.123.4444 0 Michael 147 Spadina Ave 5 515.123.5555 0 Stephen Schwanthalerstr. 7031 1 010.45.1343.329ABC 16 ... |
[ : 표현식 시작 [:alpha:] : 알파벳 문자 ] : 표현식 종료 |
지정된 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 ;
FNAME ADDR ROAD ---------- -------------------- -------------------- Jennifer 2004 Charade Rd Charade Michael 147 Spadina Ave Spadina Den 2004 Charade Rd Charade Susan 8204 Arthur St Arthur ... |
^ : 부정형 의미 ( 빈 공백이 아닌 문자 ) + : 1개 이상 □[^□]+□ : 빈 공백 문자 뒤에 공백이 아닌 문자가 하나 이상 존재하고 공백이 마지막에 붙어 있는 부분 문자 추출 ( □ => 공백 문자 ) |
정규식을 사용하여 두 번째 문자열 (Road) 추출
SQL> SELECT fname, phone,
REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code
FROM t1 ;
FNAME PHONE CODE ---------- -------------------- ---------- Jennifer 515.123.4444 123 Michael 515.123.5555 123 Den 515.127.4561 127 ... |
\. : 국번 앞,뒤로 나오는 "." ( \ => ESCAPE ) (\d{3}) : 숫자 3자리 REPLACE : 양쪽 끝의 '.' 문자를 없애기 위해 사용 |
지역번호를 뺀 국번만 추출 가능 ( 빈 공백 등으로 구분되지 않은 경우에도 가능 )
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 ;
FNAME ADDR CNT ---------- -------------------- ---------- Jennifer 2004 Charade Rd 2 Michael 147 Spadina Ave 2 Den 2004 Charade Rd 2 Susan 8204 Arthur St 0 |
'a' 가 발견 된 횟수 검색 가능
※ 동일한 결과는 다음의 문장으로도 해결 가능 하다. ( v10g 이하 )
SQL> SELECT fname, addr,
LENGTH(addr) - LENGTH(REPLACE(addr, 'a')) cnt
FROM t1 ;
FNAME ADDR CNT ---------- -------------------- ---------- Jennifer 2004 Charade Rd 2 Michael 147 Spadina Ave 2 Den 2004 Charade Rd 2 Susan 8204 Arthur St 0 |
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 ;
Position ---------- 5 |
위의 예제는 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 ;
Exp1 Exp2 Exp3 Exp4 ----- ----- ----- ----- 123 45678 56 78 |
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;
Position ---------- 198 |
단순히 tcac 의 문자만을 비교 하는 것이 아니며, "gtctcacaaag" 의 문자열이 함께 있을 때 tcac 의 위치를 반환해 줄 수 있다.
또는 앞의 예문 중에 다음의 경우도 사용 가능 하다.
SQL> SELECT fname, phone,
REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code
FROM t1 ;
FNAME PHONE CODE ---------- -------------------- ---------- Jennifer 515.123.4444 123 Michael 515.123.5555 123 Den 515.127.4561 127 ... |
국번에 해당되는 문자열을 추출 하고 있으나 앞뒤로 붙어 있는 "." 문자를 제거 하기 위해 REPLACE 를 함께 사용 하고 있다. 하위식을 사용하면 다음과 같이 실행 가능하다.
SQL> SELECT fname, phone,
REGEXP_SUBSTR(phone, '(\.)(\d{3})(\.)',1,1,'i',2) code
FROM t1 ;
FNAME PHONE CODE ---------- -------------------- ----- Jennifer 515.123.4444 123 Michael 515.123.5555 123 Den 515.127.4561 127 ... |