Oracle 10g 에서의 정규 표현식(New SQL Function)
오라클자바커뮤니티에서
설립한 개발자실무교육6년차 오엔제이프로그래밍 실무교육센터
(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링,
Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#, ASP.Net) www.onjprogramming.co.kr
이번 강좌에서는 Oracle 10g의 SQL 정규 표현식에 대해 알아 봅니다. 상당 부분 PHP라는 웹개발 언어의 정규 표현식과 상당 부분 유사한 것 같은데… 아닌가? 하나씩 알아보도록 하죠~
Oracle 8, 9i에 없는 좀더 유연한 SQL 정규 표현식은 10g에와서 많이 변화 되었습니다. 오라클 10g는 POSIX 표준에 준한 정규 표현식을 지원 합니다.
새로운 4개의 함수가 있는데 REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 등 입니다.
이들 함수는 기존 오라클의 SQL 함수인 LIKE, INSTR, SUBSTR, REPLACE와 유사하지만 10g에서 소개된 함수들은 ‘%’,’_’ 문자 대신 POSIX regular expressions를 사용 합니다. 아래는 POSIX 정규 표현의 표준 메타문자 입니다.
POSIX regular expressions consist of the standard metacharacters:
'^' for the beginning of a string
'$' for the end of string
'.' for any character
Ranges of characters, such as '[a-z]', for any ASCII lowercase letter, which is equivalent to the character class: "[[:lower:]]""
'?' allows a preceding character to match zero or one time
'+' allows a preceding character to match one or more times
'*' for zero or more times
출현 횟수에 대한 표현도 가능 한데, “{m, n}” 이 의미하는 것은 m에서부터 n번 나타난다는 의미이며 “{m}”의 경우 정확히 m번 나타남을 의미 하며, “{m,}”인 경우 적어도 m 번이상 나타남을 의미 합니다. 또한 문자들을 그룹지을 수 있는데 괄호 또는 “|” (vertical bar or pipe)를 이용하는 것이 가능 합니다. 예를들면 '^([a-z]+|[0-9]+)$'의 경우 문자열의 처음부터 끝까지 소문자 이거나 숫자가 되어야 한다는 이야기 입니다.
1. REGEXP_LIKE는 like연산자와 매우 유사 합니다. 이 함수의 경우 정규 표현식과 첫번째 아규먼트가 일치하면 TRUE를 return 합니다. 예를 보도록 하겠습니다.
WHERE 1. REGEXP_LIKE(ENAME,'^J[AO]','i') 의 경우 ename이 “JA” 또는 “JO”로 시작되는 경우가 해당 됩니다. ‘i’ 매개변수가 가 의미 하는 것은 정규 표현식이 case-insesitive 하게 만드는 역할을 합니다. (대소문자 가리지 않는 다는 것을 의미 합니다.)
또한 아래의 예문 처럼 check 제약 조건에 REGEXP_LIKE를 이용 할 수도 있습니다.
ALTER TABLE EMP ADD CONSTRAINT REGEXP_LIKE1 CHECK (REGEXP_LIKE(ENAME,'^[[:alpha:]]+$'));
위의 정규 표현식이 무얼 뜻할까요? 해석해 보면 ename컬럼에 들오 올 수 있는 값은 오로지 알파벳 문자만 가능 하며 당연히 공백이나 구두점등이 허용 되지 않음을 의미 합니다. 만약 정규 표현식을 만족하지 않는 문자를 insert, update 한다면 ORA-2290 또는 check constraint violated 오류가 발생 할겁니다.
2. REGEXP_INSTR의 경우 INSTR 함수와 비슷 합니다. 이 함수의 경우 문자열에서 정규 표현식을 만족하는 부분의 첫 번째 시작 위치를 돌려 줍니다. 예를 보도록 하겠습니다.
SELECT REGEXP_INSTR('The total is $400 for your purchase.','$[[:digit:]]+')
FROM DUAL;
아마도 14를 리턴 할 겁니다. 400의 시작 위치죠^^ 또한 출현 횟수를 지정 할 수도 있는데 위의 예인 경우 두번째로 숫자가 나타나는 부분 등에 대해 지정이 가능 합니다.
3. REGEXP_SUBSTR의 경우 정규 표현식을 만족하는 부분 문자열을 돌려 줍니다. 이것은 문자열을 직접 추출 할 때 REGEXP_INSRT과 LENGTH를 사용하여 SUBSTR하는 것 보다 훨씬 쉽게 문자열을 추출 할 수 있습니다
4. REGEXP_REPLACE의 경우 정규 표현식을 통해 REPLCAE한 후의 문자열(첫번째 아규먼트)를 돌려 줍니다.
SELECT REGEXP_REPLACE('The temperature is 23°F','([[:digit:]])+°F',('\1'-32)*5/9||'°C') FROM DUAL;
이해가 되시겠지만 이 예문의 경우 다음과 같이 출력됩니다. 'The temperature is -5°C'.
---------------------------------------------------------------
추가적으로 아래의 예문을 이해하도록 해보세요~
drop table check_reg_operator;
create table check_reg_operator
(
contact_info varchar2(100)
)
/
insert into check_reg_operator values('Contact number for smith is 238-564-7645');
insert into check_reg_operator values('Contact number for Adam is 22-269-45');
insert into check_reg_operator values('Contact number for Sumit is 64-75');
insert into check_reg_operator values('Contact number for Rajeev is 4564-564-7');
insert into check_reg_operator values('Contact number for Rajeev is sdas-767-9');
set linesize 200
select contact_info
from check_reg_operator
where regexp_like(contact_info,'..-...')
/
CONTACT_INFO
----------------------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam is 22-269-45
Contact number for Rajeev is 4564-564-7
Contact number for Rajeev is sdas-767-9
Query not to select sdas-564-7 and select only those rows having
just digits on both sides of '-'
select contact_info
from check_reg_operator
where regexp_like(contact_info,'[0-9]{2}-[0-9]{3}')
/
CONTACT_INFO
-------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam is 22-269-45
Contact number for Rajeev is 4564-564-7
댓글 없음:
댓글 쓰기