데이터베이스
정규 표현식 (POSIX /PERL)
ssseung
2021. 5. 17. 01:22
정규표현식은 문자열의 규칙을 표현하는 검색 패턴으로 주로 문자열 검색과 치환에 사용된다.
1. POSIX 연산자
| 연산자 | 영문 | 설명 |
| . | dot | 모든 문자와 일치 |
| | | or | 대체 문자를 구분 |
| \ | backslash | 다음 문자를 일반 문자로 취급 |
select -- dot 이 있는 자리에 어떤 문자도 올 수 있다.
REGEXP_SUBSTR('aab','a') as C1 -- a
,REGEXP_SUBSTR('aab','a.b') as C2 --aab
,REGEXP_SUBSTR('aab','a.') as C3 -- aa
,REGEXP_SUBSTR('aab','.b') as C4 -- ab
,REGEXP_SUBSTR('abb','a.b') as C5 -- abb
,REGEXP_SUBSTR('acb','a.b') as C6 -- acb
,REGEXP_SUBSTR('adc','a.b') as C7 -- null 패턴 불일치
from dual;-- 일치하는 문자를 반환한다.
SELECT REGEXP_SUBSTR ('a','a|b') AS C1 -- a
,REGEXP_SUBSTR ('b','a|b') AS C2 -- b
,REGEXP_SUBSTR ('c','a|b') AS C3 -- null
,REGEXP_SUBSTR ('ab','ab|cd') AS C4 -- ab
,REGEXP_SUBSTR ('cd','ab|cd') AS C5 -- cd
,REGEXP_SUBSTR ('bc','ab|cd') AS C6 -- null
,REGEXP_SUBSTR ('aa','a|aa') AS C7 -- 첫번째 일치 a
,REGEXP_SUBSTR ('aa','aa|a') AS C8 -- aa
FROM DUAL;
SELECT REGEXP_SUBSTR ('a|b','a|b') AS C1 -- a
,REGEXP_SUBSTR ('a|b','a\|b') AS C2 -- a|b
FROM DUAL;
앵커(anchor) 는 검색 패턴의 시작과 끝을 지정한다.
| 연산자 | 영문 | 설명 |
| ^ | carrot | 문자열의 시작 |
| $ | dollar | 문자열의 끝 |
* CHR(10) 은 개행 문자를 반환한다.
C1 은 전체 문자열의 시작인 a 를 ,
C2 은 전체 문자열의 두 번째 시작 문자가 존재하지 않아 NULL 을 반환한다.
C3 은 전체 문자열의 끝인 d를 ,
C4 은 전체 문자열의 두 번째 끝 문자가 존재하지 않아 NULL 을 반환한다.
-- REGEXP_SUBSTR(대상 문자, 패턴, 검색시작위치 1 , 패턴 일치 횟수 1)
SELECT REGEXP_SUBSTR ('ab' || CHR(10) || 'cd' , '^.', 1, 1 ) AS C1 -- a
,REGEXP_SUBSTR ('ab' || CHR(10) || 'cd' , '^.', 1, 2 ) AS C2 -- null
,REGEXP_SUBSTR ('ab' || CHR(10) || 'cd' , '.$', 1, 1 ) AS C3 -- d
,REGEXP_SUBSTR ('ab' || CHR(10) || 'cd' , '.$', 1, 2 ) AS C4 -- null
FROM DUAL;
수량사는 선행 표현식의 일치 횟수를 지정한다. 패턴을 최대로 일치시키는 탐욕적 방식으로 동작한다.
| 연산자 | 설명 |
| ? | 0회 혹은 1회 일치 |
| * | 0회 혹은 그 이상의 횟수로 일치 |
| + | 1회 또는 그 이상의 횟수로 일치 |
| {m} | m회 일치 |
| {m,} | 최소 m회 일치 |
| {,m} | 최대 m회 일치 |
| {m,n} | 최소 m회, 최대 n회 일치 |
SELECT REGEXP_SUBSTR ('ac','ab?c' ) AS C1 -- b가 0회 => ac
,REGEXP_SUBSTR ('abc','ab?c' ) AS C2 -- b가 1회 => abc
,REGEXP_SUBSTR ('abbc','ab?c' ) AS C3 -- b가 2회 => null 반환
,REGEXP_SUBSTR ('ac','ab*c' ) AS C4 -- b가 0회 ok => ac
,REGEXP_SUBSTR ('abc','ab*c' ) AS C5 -- b가 1회 ok => abc
,REGEXP_SUBSTR ('abbc','ab*c' ) AS C6 -- b가 2회 ok =>abbc
,REGEXP_SUBSTR ('ac','ab+c' ) AS C7 -- b가 0회 null 반환
,REGEXP_SUBSTR ('abc','ab+c' ) AS C8 -- abc
,REGEXP_SUBSTR ('abbc','ab+c' ) AS C9 -- abbc
FROM DUAL;
================================================
일치가능대상
ab?c => b가 0 or 1회 일치 (ac, abc)
ab*c => b가 0 or more회 일치 (ac,abc,abbc ..)
ab+c => b가 1 or more회 일치 (abc,abbc,abbbbc,abbbbbc ..
SELECT REGEXP_SUBSTR ('ab','a{2}' ) AS C1 -- a 2회 일치 x -> null 반환
,REGEXP_SUBSTR ('aab','a{2}' ) AS C2 --aa
,REGEXP_SUBSTR ('aab','a{3,}' ) AS C3 -- a 최소 3회 일치 x -> null 반환
,REGEXP_SUBSTR ('aaab','a{3,}' ) AS C4 --aaa
,REGEXP_SUBSTR ('aaab','a{4,5}' ) AS C5 -- a 최소4, 최대5회 일치 x -> null 반환
,REGEXP_SUBSTR ('aaaab','a{4,5}' ) AS C6 -- aaaa
FROM DUAL;
================================================
일치가능대상
ab?c => b가 0 or 1회 일치 (ac, abc)
ab*c => b가 0 or more회 일치 (ac,abc,abbc ..)
ab+c => b가 1 or more회 일치 (abc,abbc,abbbbc,abbbbbc ..
서브표현식
표현식에 괄호로 묶어 서브표현식으로 만들고, 임의의 횟수만큼 반복해 사용할 수도 있다.
| 연산자 | 설명 |
| (expr) | 괄호 안의 표현식을 하나의 단위로 취급 |
다음은 서브 표현식을 사용한 쿼리다.
SELECT REGEXP_SUBSTR ('ababc','(ab)+c' ) AS C1 -- ababc
,REGEXP_SUBSTR ('ababc','ab+c' ) AS C2 -- abc
,REGEXP_SUBSTR ('abd','a(b|c)d' ) AS C3 -- abd
,REGEXP_SUBSTR ('abd','ab|cd' ) AS C4 -- ab
FROM DUAL;
================================================
일치가능대상
(ab)+c => ab가 1회 이상 반복 (abc,ababc, .. )
ab+c => b가 1회 이상 반복 (abc,abbc,abbbc, ..)
a(b|c)d => b나 c가 대체 (abd,acd)
ab|cd => ab 나 cd가 대체 (ab,cd)
\n 역참조를 사용하면 일치한 서브 표현식을 다시 참조할 수 있다.
반복되는 패턴을 검사하거나 서브 표현식의 위치를 변경하는 용도로 사용할 수 있다.
| 연산자 | 설명 |
| \n | n번째 서브 표현식과 일치, n은 1~9까지의 정수 |
SELECT REGEXP_SUBSTR ('abxab','(ab|cd)x\1' ) AS C1 -- \1 > 괄호 내 서브표현을 1회 반복 abxab
,REGEXP_SUBSTR ('cdxcd','(ab|cd)x\1' ) AS C2 -- cdxcd
,REGEXP_SUBSTR ('abxef','(ab|cd)x\1' ) AS C3 -- null
,REGEXP_SUBSTR ('ababab','(.*)\1+' ) AS C4 -- \1+> 괄호 내 서브표현을 1회 이상 반복 ababab
,REGEXP_SUBSTR ('abcabc','(.*)\1+' ) AS C5 -- abcabc
,REGEXP_SUBSTR ('abcabd','(.*)\1+' ) AS C6 -- null
FROM DUAL;
================================================
일치가능대상
(ab|cd)x\1 => abxab , cdxcd 문자열과 일치
(.*)\1+ => 동일 문자열이 1회 이상 반복되는 패턴
문자리스트는 대괄호로 묶은 표현식이다.
문자리스트 중 한 문자만 일치하면패턴이 일치한 것으로 처리된다.
문자 리스트에서 - 하이픈은 범위 연산자로 동작한다.
| 연산자 | 설명 | 동일 |
| [:digit:] | 숫자 | [0-9] |
| [:lower:] | 소문자 | [a-z] |
| [:upper:] | 대문자 | [A-Z] |
| [:alpha:] | 영문자 | [a-zA-Z] |
| [:alnum:] | 영문자와 숫자 | [0-9a-zA-Z] |
| [:xdigit:] | 16진수 | [0-9a-fA-F] |
| [:punct:] | 구두점 기호 | [^[:alnum:][:cntrl:]] |
| [:blank:] | 공백 문자 | |
| [:space:] | 공간문자 (스페이스,엔터,탭) |
2. PERL 정규 표현식 연산자
| 연산자 | 설명 | 동일 |
| \d | 숫자 | [[:digit:]] |
| \D | 숫자가 아닌 모든 문자 | [^[:digit:]] |
| \w | 숫자와 영문자(언더바 포함) | [[:alnum:]_] |
| \W | 숫자와 영문자가 아닌 모든 문자 (언더바 제외) |
[^[:alnum:]_] |
| \s | 공백 문자 | [[:space:]] |
| \S | 공백 문자가 아닌 모든 문자 | [^[:space:]] |
SELECT REGEXP_SUBSTR ('(650)555-0100', '^\(\d{3}\) \d{3}-\d{4}$' ) AS C1 -- (650)555-0100
,REGEXP_SUBSTR ('650-555-0100', '^\(\d{3}\) \d{3}-\d{4}$' ) AS C2 -- null
,REGEXP_SUBSTR ('b2b','\w\d\D' ) AS C3 -- b2b
,REGEXP_SUBSTR ('b2_','\w\d\D' ) AS C4 -- b2_
,REGEXP_SUBSTR ('b22','\w\d\D' ) AS C5 -- null
FROM DUAL;
================================================
일치가능대상
^\(\d{3}\) \d{3}-\d{4}$ =>
(\d{3}) \d{3} \d{4} $(문자끝)
\w\d\D => 문자/숫자(언더바포함) + 숫자 + 문자
SELECT REGEXP_SUBSTR ('jdoe@company.co.uk', '\w+@\w+(\.\w+)+' ) AS C1
,REGEXP_SUBSTR ('jdoe@company', '\w+@\w+(\.\w+)+' ) AS C1 -- null
,REGEXP_SUBSTR ('to : bill','\w+\W\s\w+' ) AS C3
,REGEXP_SUBSTR ('to bill','\w+\W\s\w+' ) AS C4 -- null
FROM DUAL;
===========================================
일치가능대상
\w+@\w+(\.\w+)+ =>
숫자문자@숫자문자 + (.숫자문자) 1회이상
\w+\W\s\w+ => 숫자문자 + 숫자문자가 아닌문자 + 공백문자 + 숫자문자
PERL 정규 표현식 연산자는 수량사와 유사하게 동작한다.
패턴을 최소로 일치시키는 비탐욕적(nongreedy) 방식으로 동작한다.
| 연산자 | 설명 |
| ?? | 0회 또는 1회 일치 |
| *? | 0회 또는 그 이상 일치 |
| +? | 1회 또는 그 이상 일치 |
| {m}? | m회 일치 |
| {m,}? | 최소 m회 일치 |
| {,m}? | 최대 m회 일치 |
| {m,n}? | 최소 m, 최대 n회 일치 |
SELECT REGEXP_SUBSTR ('aaaa', 'a??aa' ) AS C1 -- nongreedy 방식/ 최소 일치
,REGEXP_SUBSTR ('aaaa', 'a?aa' ) AS C2 -- greedy 방식/ 최대 일치
,REGEXP_SUBSTR ('xaxbxc', '\w*?x\w' ) AS C3
,REGEXP_SUBSTR ('xaxbxc', '\w*x\w' ) AS C4
,REGEXP_SUBSTR ('abxcxd', '\w+?x\w' ) AS C5
,REGEXP_SUBSTR ('abxcxd', '\w+x\w' ) AS C6
FROM DUAL;
===========================================
c1 c2 c3 c4 c5 c6
-- -- -- -- -- --
aa aaa xa xaxbxc abxc abxcxd
SELECT REGEXP_SUBSTR ('aaaa', 'a{2}?' ) AS C1 -- nongreedy 방식
,REGEXP_SUBSTR ('aaaa', 'a{2}' ) AS C2 -- greedy 방식
,REGEXP_SUBSTR ('aaaaa', 'a{2,} ?' ) AS C3 -- 최소 일치
,REGEXP_SUBSTR ('aaaaa', 'a{2,}' ) AS C4 -- 최대 일치
,REGEXP_SUBSTR ('aaaaa', 'a{2,4}?' ) AS C5 -- 최소
,REGEXP_SUBSTR ('aaaaa', 'a{2,4}' ) AS C6 -- 최대
FROM DUAL;
===========================================
c1 c2 c3 c4 c5 c6
-- -- -- -- -- --
aa aa aa aaaaa aa aaaa
정규표현식 조건과 함수
REGEXP_LIKE 조건
source_char 가 pattern 과 일치하면 TRUE, 아니면 FALSE 를 반환한다.
REGEXP_LIKE (source_char,pattern [,match param])match param 은 일치 옵션을 지정한다.
i : 대소문자 무시
c: 대소문자 구분 --기본값이다.
n: dot(.) 개행문자와 일치
m: 다중행 모드
x 검색 패턴의 공백 문자를 무시
icnmx 형식으로 다수의 옵션을 함께 지정할 수 있다.
SELECT FIRST_NAME, LAST_NAME
FROM HR.EMPLOYEE
WHERE REGEXP_LIKE (FIRST_NAME, '^Ste(v|ph)en$');
=================================================
FIRST_NAME LAST_NAME
---------- ----------
Stephen Stiles
Steven King
Steven Markle
REGEXP_REPLACE
source_char 에서 일치한 pattern 을 replace_string 으로 변경한 문자 값을 반환한다.
REGEXP_REPLACE (source_char, pattern [,replace_string [,position [,occurrence [,match_param]]]])
position 은 검색 시작 위치를 지정한다. 기본값은 1
occurrence 는 패턴 일치 횟수를 지정한다. 기본값은 1
SELECT PHONE_NUMBER
,REGEXP_REPLACE(
PHONE_NUMBER
,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})'
,'(\1) \2-3'
) AS C1
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID IN (144,145);
==============================================
PHONE_NUMBER C1
------------- -----------
650.121.2004 (650) 121-2004
011.44.1344.429268 011.44.1344.429268
REGEXP_SUBSTR
source_char 에서 일치한 pattern 을 반환한다.
REGEXP_SUBSTR (source_char, pattern [,position [,occurrence [,match_param [, subexpr]]]])
-- (대상 문자, 패턴, 검색시작위치 ,패턴 일치 횟수, 일치 옵션 ,서브표현식)subexpr 은 서브 표현식을 지정한다. 0은 전체 패턴, 1이상은 서브 표현식, 기본값을 0
SELECT REGEXP_SUBSTR('http://www.example.com/products',
,'http://([[:alnum]]+\.?{3,4}/?') AS C1
FROM DUAL;
==============================
C1
----
http://www.example.com
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형