오라클 정규식 사용 팁

★ 오라클 정규식 사용 팁
Oracle 10g에서는 REGEXP_로 시작하는 함수를 지원합니다.
Regular Expression이라고 하죠~

with t as (

SELECT 'hellokitty@empal.com' email

    , '555.123.4567' tel 

    , '0107081234567' minnum

    , 'Oracle is the   Information   Company' txt

FROM dual

union all

SELECT 'manpro1@naver.com' email

    , '064.321.7654' tel 

    , '7107081234567' minnum

    , 'O  racle is the   Information   C    ompany' txt

FROM dual

)

SELECT REGEXP_SUBSTR(email, '[^@]+', 1, 1) AS "ID"

    , REGEXP_SUBSTR(email, '[^@]+', 1, 2) AS "eAddr"

    , REGEXP_REPLACE(tel,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','(\1) \2 - \3') AS "Tel"

    , REGEXP_REPLACE(minnum, '[0-9]', '*', 7) AS "SSN"

    , REGEXP_REPLACE(txt, '( ){2,}', ' ') AS "txt_blank"

    , REGEXP_INSTR(txt, 'I') AS "REGEXP_INSTR"

FROM t

WHERE REGEXP_LIKE(email, '[[:digit:]]');

1. REGEXP_LIKE
- LIKE 연산자와 유사하며, 표현식 패턴(Regular Expression Pattern)을 수행하여, 일치하는 값을 반환합니다.
- 문법 : REGEXP_LIKE(srcstr, pattern [,match_option])
- srcstr : 소스 문자열, 검색하고자 하는 값.

- pattern : Regular Expression Operator를 통해 문자열에서 특정 문자를 보다 다양한 pattern으로 검색하는 것이 가능.
- match_option : match를 시도할 때의 옵션. 찾고자 하는 문자의 대소문자 구분이 기본으로 설정. 대소문자를 구분할 필요가 없다면 'i' 옵션 사용을 지정한다.
[[:digit:]] : 숫자인 것.
[^[:digit:]] : 숫자가 아닌 것.

[^expression] : expression의 부정.

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

SELECT SSN FROM test WHERE REGEXP_LIKE(SSN, '[^[:digit:]]'); SSN -------------------- ******2229198 ******2047888 ******2349876

- 예제 2 : 제품의 이름 중에 'SS' 다음에 'P'를 포함하지 않은 문자열을 찾자.

SELECT product_name FROM oe.product_information WHERE REGEXP_LIKE(product_name, 'SS[^P]');

- 예제 3 : 제품 이름에 'SS' 다음 'P'나 'S'를 포함하는 문자열을 찾자.

SELECT product_name FROM oe.product_information WHERE REGEXP_LIKE(product_name, 'SS[PS]');

 

- 예제 4 : 메일 주소에 '@'이 포함되도록 설정.

ALTER TABLE customers ADD CONSTRAINT cust_email_addr CHECK(REGEXP_LIKE(cust_email, '@')) NOVALIDATE;

※ 참고 : 제약조건 해제 ALTER TABLE customers DROP CONSTRAINT cust_email_addr;

 

 

2. REGEXP_SUBSTR
- SUBSTR 함수의 기능을 확장하였습니다. 주어진 문자열을 대상으로 정규 표현식 패턴을 수행하여, 일치하는 하위 문자열을 반환합니다.
- 문법 : REGEXP_SUBSTR(srcstr, pattern, [,position[,occurrence[,match_option]]])
- srcstr : 소스 문자열
- position : Oracle이 문자열에서 특정 문자를 어디에서 찾아야 하는지 위치를 나타냄. 기본으로 1로 설정되어 있으므로, 문자열의 처음부터 검색을 시작.
- occurrence : 검색하고자 하는 문자열에서 특정 문자의 발생 횟수. 기본으로 1로 설정되어 있으며, 이는 Oracle이 문자열에서 첫번째 발생 pattern을 찾는다는 의미.
- match_option : match를 시도할 때의 옵션
- 예제

SELECT REGEXP_SUBSTR(email, '[^@]+', 1, 1) AS "ID" , REGEXP_SUBSTR(email, '[^@]+', 1, 2) AS "MailAddr" FROM ( SELECT 'hellokitty@empal.com' email FROM dual ); ID MailAddr --------------------- hellokitty empal.com

 

 

3. REGEXP_REPLACE
- 주어진 문자열을 대상으로 정규 표현식 패턴을 조사하여, 다른 문자로 대체합니다.
- 문법 : REGEXP_REPLACE(srcstr, pattern [,replacestr[,position[,occurrence[,match_option]]]])
- replacestr : 대체하고자 하는 문자열을 나타냅니다.
- 예제 1 : 주민번호 뒷자리를 '*'로 표현

SELECT REGEXP_REPLACE(SSN, '[0-9]', '*', 7) AS "SSN" FROM ( SELECT '7907051234567' SSN FROM dual ); SSN ------------- 790705*******

- 예제 2 : 둘 이상의 공백 문자를 하나로 대체하여 가독성을 높이자.

SELECT REGEXP_REPLACE('Oracle is the Information Company', '( ){2,}', ' ') AS "Result" FROM dual; RESULT ----------------------------------- Oracle is the Information Company

- 예제 3 : 전화번호의 표현 방식을 3자리, 3자리, 4자리로 묶어 식별력을 높이자.

SELECT REGEXP_REPLACE('555.123.4567','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','(\1) \2 - \3') AS "Result1" FROM dual;   Result1 ---------------- (555) 123 - 4567

-. [:digit:] : 숫자 값.
-. [:alpha:] : 문자 값.
-. [:alnum:] : 문자와 숫자를 함께 사용.

 

4. REGEXP_INSTR
- 정규 표현을 만족하는 부분의 위치를 반환합니다.
- 문법 : REGEXP_INSTR(srcstr, pattern [,position[,occurrence[,returnparam[,match_option]]]])
- position : 검색 시작 위치
- occurrence : 발생 횟수.
- returnparam : 반환 옵션.
- match_option : match를 시도할 때의 옵션.
-. 예제

SELECT REGEXP_INSTR('Regular Expression', 'a') AS "REGEXP_INSTR" FROM dual; REGEXP_INSTR --------------- 6

 

※ 참고 site

Oracle Database Documentation Library

http://www.oracle.com/pls/db10g/homepage

C Oracle Regular Expression Support

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_posix.htm

출처: http://cheezred.tistory.com/35 [망할고양이의 도피처]