오라클 정규식 사용 팁
★ 오라클 정규식 사용 팁
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 SSN |
- 예제 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"
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" SSN |
| SELECT REGEXP_REPLACE('Oracle is the Information Company', '( ){2,}', ' ') AS "Result" FROM dual; RESULT ----------------------------------- Oracle is the Information Company |
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 [망할고양이의 도피처]