SQL활용 - 오라클 정규표현식 총정리 (POSIX 정규표현식 연산자, REGEXP_SUBSTR, PEAR 정규표현식 연산자, REGEXP_LIKE 조건절, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT)

 

SQL활용 - 오라클 정규표현식 총정리 (POSIX 정규표현식 연산자, REGEXP_SUBSTR, PEAR 정규표현식 연산자, REGEXP_LIKE 조건절, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT)

 

 

정규 표현식은 문자열의 규칙을 표현하는 검색패턴으로 주로 문자열 검색과 치환에 사용됩니다.

POSIX 연산자

- . : 모든 문자와 일치
- | : 대체 문자를 구분
- \ : 다음 문자를 일반 문자로 취급

- ^ (Carrot) : 문자열의 시작
- $ (Dollar) : 문자열의 끝

 

REGEXP_SUBSTR

 

REGEXP_SUBSTR 함수는 문자열에서 일치하는 패턴을 반환하는 함수입니다. 

 

아래의 쿼리는 . 연산자를 사용한 쿼리입니다. c1,c2,c3,c4 열의 . 연산자는 각각 a,b,c,d 문자와 일치한다.

- c4는 세번째 문자가 패턴과 일치하지 않기때문에 NULL을 반환한다.

 

SELECT
REGEXP_SUBSTR ('aab', 'a.b') AS C1
,REGEXP_SUBSTR ('abb', 'a.b') AS C2
,REGEXP_SUBSTR ('acb', 'a.b') AS C3
,REGEXP_SUBSTR ('adc', 'a.b') AS C4 -- 정규표현식과 일치하지 않기 때문에 NULL을 반환한다.
FROM DUAL;

 

다음은 OR(|) 연산자를 사용한 쿼리입니다. OR(|) 연산자는 기술 순서에 따라 패턴을 일치시킵니다.

 

SELECT
REGEXP_SUBSTR ('a', 'a|b') AS C1
,REGEXP_SUBSTR ('b', 'a|b') AS C1
,REGEXP_SUBSTR ('c', 'a|b') AS C1
,REGEXP_SUBSTR ('ab', 'ab|cd') AS C1
,REGEXP_SUBSTR ('cd', 'ab|cd') AS C1
,REGEXP_SUBSTR ('bc', 'ab|cd') AS C1
,REGEXP_SUBSTR ('aa', 'a|aa') AS C1
,REGEXP_SUBSTR ('aa', 'aa|a') AS C1
FROM DUAL;

 

다음은 BACKSLASH(\) 연산자를 사용한 쿼리입니다. 바로 다음에 오는 특수문자를 일반문자로 취급합니다.

 

SELECT
REGEXP_SUBSTR ('a|b', 'a|b') AS C1
,REGEXP_SUBSTR ('a|b', 'a\|b') AS C2
FROM DUAL;

 

수량사는 선행 표현식의 일치 횟수를 지정합니다. 패턴을 최대로 일치시키는 탐욕적인 방식으로 동작합니다.

 

- ? : 0 회 또는 1회 일치
- * : 0회 또는 그 이상 횟수로 일치
- + : 1회 또는 그 이상 횟수로 일치

 

SELECT
-- ab?c :  a와 c 사이에 b가 0회 또는 1회 위치하는지 판별
REGEXP_SUBSTR('ac', 'ab?c') AS C1
,REGEXP_SUBSTR('abc', 'ab?c') AS C2 -- abc
,REGEXP_SUBSTR('abbc', 'ab?c') AS C3 -- null
-- ab*c : a와 c 사이에 b가 0회 또는 그 이상 횟수로 위치
,REGEXP_SUBSTR('ac', 'ab*c') AS C4 -- ac
,REGEXP_SUBSTR('abc', 'ab*c') AS C5 -- abc
,REGEXP_SUBSTR('abbc', 'ab*c') AS C6 -- abbc
-- ab+c : a와 c 사이에 b가 1회이상 위치하는지 판별
,REGEXP_SUBSTR('ac', 'ab+c') AS C7 -- null
,REGEXP_SUBSTR('abc', 'ab+c') AS C8 -- abc
,REGEXP_SUBSTR('abbc', 'ab+c') AS C9 -- abbc
FROM DUAL;

 

- {m} : m회 일치
- {m,} : 최소 m회 일치
- {,m} : 최대 m회 일치
- {m,n} : 최소 m회, 최대 n회 일치

 

SELECT
-- a{2} : a가 2회 반복
REGEXP_SUBSTR('ab', 'a{2}') AS C1 -- null
,REGEXP_SUBSTR('aab', 'a{2}') AS C2 -- aab

-- a{3} : a가 3회 이상 반복
,REGEXP_SUBSTR('aab', 'a{3,}') AS C3 -- null
,REGEXP_SUBSTR('aaab', 'a{3,}') AS C4 -- aaab

-- a{4,5} a가 최소 3회 이상 4회 이하로 반복
,REGEXP_SUBSTR('aaab', 'a{4,5}') AS C5 -- aaab
,REGEXP_SUBSTR('aaaab', 'a{4,5}') AS C6 -- aaaab
FROM DUAL;

 

- (expr) : 괄호 안의 표현식을 하나의 단위로 취급합니다.

 

SELECT
-- ab 가 1회이상 반복
REGEXP_SUBSTR('ababc', '(ab)+c') AS C1
-- b가 1회 이상 반복되야 참
,REGEXP_SUBSTR('ababc', 'ab+c') AS C2
-- b 또는 c 가 1회 이상 반복되야 참
,REGEXP_SUBSTR('abd', 'a(b|c)d') AS C3
-- ab 또는 cd 와 일치해야 참
,REGEXP_SUBSTR('abd', 'ab|cd') AS C4
FROM DUAL;

 

역 참조를 사용하면 일치한 서브 표현식을 다시 참조할 수 있습니다.

- \n : n번째 서브 표현식과 일치, n은 1에서 9사이의 정수

 

SELECT
-- ab 또는 cd 패턴이 한 번 더 반복
REGEXP_SUBSTR('abxab', '(ab|cd)x\1') AS C1
-- ab 또는 cd 패턴이 한 번 더 반복
,REGEXP_SUBSTR('abxef', '(ab|cd)x\1') AS C3
-- 동일한 패턴이 1회 이상 반복
,REGEXP_SUBSTR('ababab', '(.*)\1+') AS C4
-- 동일한 패턴이 1회 이상 반복되어야 참
,REGEXP_SUBSTR('abcabd', '(.*)\1+') AS C6
FROM DUAL;

 

- 문자리스트는 문자를 대괄호로 묶은 표현식입니다.
- 문자 리스트 중 한 문자만 일치하면 패턴을 만족하는 것으로 처리한다. 하이픈(-)은 범위 연산자로 동작합니다.

- [char..] : 문자 리스트 중 한 문자와 일치
- [^char..] : 문자 리스트에 포함되지 않은 한 문자와 일치

 

SELECT
-- c 앞 문자가 ab 중에 한 문자와 일치
REGEXP_SUBSTR('ac', '[ab]c') AS C1 -- ac
,REGEXP_SUBSTR('cc', '[ab]c') AS C3 -- null
-- c앞의 문자가 ab 중에 없는 문자와 일치
,REGEXP_SUBSTR('ac', '[^ab]c') AS C4 -- null
-- ac 또는 bc 가 아닌 문자열
,REGEXP_SUBSTR('cc', '[^ab]c') AS C6 -- cc
FROM DUAL;

 

다음은 문자 리스트의 범위 연산자를 사용한 쿼리입니다. 문자 리스트는 문자를 대괄호로 묶은 표현식입니다. 문자 리스트 중 한 문자와 일치하면 패턴이 일치한 것으로 처리합니다. 문자 리스트에서 하이픈은 범위 연산자로 동작합니다.

 

SELECT
REGEXP_SUBSTR('1a', '[0-9][a-z]') AS C1
,REGEXP_SUBSTR('9z', '[0-9][a-z]') AS C2
,REGEXP_SUBSTR('aA', '[^0-9][^a-z]') AS C3
,REGEXP_SUBSTR('Aa', '[^0-9][^a-z]') AS C4
FROM DUAL;

 

문자열 리스트는 다음과 같이 사용할 수 있습니다.

 

- [:digit:] : 숫자
- [:lower:] : 소문자
- [:upper:] : 대문자
- [:alpha:] : 영문자
- [:alnum:] : 영문자와 숫자
- [:xdigit:] : 16진수
- [:punct:] : 구두점 기호
- [:blank:] : 공백 문자
- [:space:] : 공간문자

 

SELECT
REGEXP_SUBSTR('gF1', '[[:digit:]]') AS C1
,REGEXP_SUBSTR('gF1', '[[:alpha:]]') AS C2
,REGEXP_SUBSTR('gF1', '[[:lower:]]') AS C3
,REGEXP_SUBSTR('gF1', '[[:upper:]]') AS C4
,REGEXP_SUBSTR('gF1', '[[:alnum:]]') AS C5
,REGEXP_SUBSTR('gF1', '[[:xdigit:]]') AS C6
,REGEXP_SUBSTR('gF1', '[[:punct:]]') AS C7
FROM DUAL;

 

PERL 정규 표현식 연산자

 

PERL 정규 표현식 연산자는 다음과 같은 의미가 있습니다.

 

- \d : 숫자
- \D : 숫자가 아닌 모든 문자
- \w : 숫자와 영문자
- \W : 숫자와 영문자가 아닌 모든 문자
- \s : 공백 문자
- \S : 공백 문자가 아닌 모든 문자

 

아래는 \d 와 \D 연산자를 사용한 쿼리입니다.

 

SELECT
-- (숫자 3자리) 숫자3자리-숫자4자리
REGEXP_SUBSTR('(650) 555-0100', '\(\d{3}\) \d{3}-\d{4}') AS C1
-- (숫자 3자리) 숫자3자리-숫자4자리
,REGEXP_SUBSTR('650-555-0100', '^\(\d{3}\) \d{3}-\d{4}$') AS C2
-- 숫자or영문자 숫자 문자
,REGEXP_SUBSTR('b2b', '\w\d\D') AS C3
,REGEXP_SUBSTR('b2_', '\w\d\D') AS C4
,REGEXP_SUBSTR('b22', '\w\d\D') AS C5
FROM DUAL;

 

\w 와 \W 연산자를 사용한 쿼리입니다.

 

SELECT
-- 1회이상 숫자나영문자@1회이상 숫자나영문자(.1회이상 숫자나영문자) 1회이상 반복
REGEXP_SUBSTR('jdoe@company.co.uk', '\w+@\w+(\.\w+)+') 

-- 숫자문자+ 숫자문자가 아닌 문자 + 공백문자 + 숫자문
,REGEXP_SUBSTR('to: boll', '\w+\W\s\w+') AS C3
,REGEXP_SUBSTR('to boll', '\w+\W\s\w+') AS C4
FROM DUAL;

 

\s 연산자와 \S연산자를 사용한 쿼리입니다.

 

SELECT
REGEXP_SUBSTR('(a b )','\(\w\s\w\s\)')
,REGEXP_SUBSTR('(a b )','\(\w\S\w\S\)')
,REGEXP_SUBSTR('(a,b.)','\(\w\s\w\s\)')
,REGEXP_SUBSTR('(a,b.)','\(\w\S\w\S\)')
FROM DUAL;

 

PEAL 정규 표현식 연산자는 수량사와 유사하게 동작합니다. 패턴을 최소로 일치시키는 비탐욕적 방식으로 동작합니다.

 

- ?? : 0회 또는 1회 일치
- *? : 0회 또는 그 이상의 횟수로 일치
- +? : 1회 또는 그 이상의 횟수로 일치
- {m}? : m회 일치
- {m,}? : 최소 m회 일치
- {,m}? : 최대 m회 일치
- {m,n}? : 최소 m회, 최대 n회 일치.

 

다음은 ??, *?, +? 연산자를 사용한 쿼리입니다.

 

SELECT
-- ?? 연산자는 패턴을 최소로 일치시키는 non-greedy 방식을 사용합니다.
REGEXP_SUBSTR('aaaa','a??aa') -- aa
-- ? 연산자는 패턴을 최대로 일치시키는 greedy 방식을 사용합니다.
,REGEXP_SUBSTR('aaaa','a?aa') -- aaa
,REGEXP_SUBSTR('xaxbxc','\w*?x\w') -- xa
,REGEXP_SUBSTR('xaxbxc','\w*x\w') -- xaxbxc
,REGEXP_SUBSTR('abxcxd','\w*+?x\w') - abxc
,REGEXP_SUBSTR('abxcxd','\w+x\w') --abxcxd
FROM DUAL;

 

{m}?, {m,}? , {m,m}? 연산자를 사용한 쿼리입니다.

 

SELECT
REGEXP_SUBSTR('aaaa','a{2}?') -- aa
,REGEXP_SUBSTR('aaaa','a{2}') -- aa
,REGEXP_SUBSTR('aaaaa','a{2,}?') -- aa
,REGEXP_SUBSTR('aaaaa','a{2,}') -- aaaaa
,REGEXP_SUBSTR('aaaaa','a{2,4}?') -- aa
,REGEXP_SUBSTR('aaaaa','a{2,4}') -- aaaa
FROM DUAL;

 

REGEXP_LIKE 조건

 

REGEXP_LIKE 함수는 아래와 같은 형태입니다. 조건절에 사용해서 source_char 가 pattern 과 일치하면 TRUE, 일치하지 않으면 FALSE를 반환합니다.

 

- MATCH_PARAM 은 일치 옵션을 지정합니다.

 

REGEXP_LIKE (SOURCE_CHAR, PATTERN [, MATCH_PARAM])

 

REGEXP_LIKE 조건을 사용한 쿼리입니다. First_name 값이 Ste로 시작하고 v 나 ph 다음에 en으로 끝나는 행을 검색하다.

 

SELECT FIRST_NAME, LAST_NAME
FROM HR.EMPLOYEES
WHERE REGEXP_LIKE(FIRST_NAME, '^Ste(v|ph)en$');

 

REGEXP_REPLACE 함수

 

REGEXP_REPLACE 함수는 아래와 같은 형태입니다. source_char 에서 일치한 pattern 을 replace_string으로 변경한 문자 값을 변환합니다.

 

- replace_string은 변경 문자열을 지정합니다.
- position 은 검색 시작 위치를 지정합니다.
- occurrence 는 패턴 일치 횟수를 지정합니다.

 

REGEXP_REPLACE(source_char, pattern [, replace_string[, position [, occurrence [, match_param]]]])

 

'숫자3자리.숫자3자리.숫자4자리 '패턴을 '(첫 번째 일치) 두 번째 일치 - 세 번째 일치' 형식으로 변경합니다. 일치한 패턴이 없으면 원본 값을 반환합니다.

 

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);

 

 

REGEXP_SUBSTR 함수

 

REGEXP_SUBSTR 는 아래와 같은 형태입니다. SOURCE_CHAR 에서 일치한 pattern을 반환합니다.

 

- subexpr 은 서브표현식을 지정합니다. (0은 전체 패턴, 1 이상은 서브표현식. 기본값 0)

 

REGEXP_SUBSTR(source_char, pattern [,position [, occurrence [, match_param [, subexpr]]]])

 

SELECT REGEXP_SUBSTR ('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?')
FROM DUAL;

-- 일치한 서브 표현식을 반환한다.
SELECT

REGEXP_SUBSTR('12345678909', '(123)(4(56)(78))', 1, 1, 'i', 1) AS C1 -- 123
, REGEXP_SUBSTR('12345678909', '(123)(4(56)(78))', 1, 1, 'i', 4) AS C2 -- 78
FROM DUAL

 

REGEXP_INSTR 함수

 

REGEXP_INSTR 함수는 source_char 에서 일치한 pattern의 시작 위치를 정수로 반환합니다.

 

- return_opt 는 반환 옵션입니다. (0은 시작위치, 1은 다음 위치)

 

REGEXP_INSTR(source_char, pattern [ , position [, occurrence [, return_opt [, match_param [, subexpr]]]])

 

서브 표현식의 시작 위치를 반환합니다.

 

SELECT
  -- 1
  REGEXP_INSTR('1234567890','(123)(4(56)(78))', 1, 1, 0, 'i', 1),
  -- 4
  REGEXP_INSTR('1234567890','(123)(4(56)(78))', 1, 1, 0, 'i', 2),
  -- 7
  REGEXP_INSTR('1234567890','(123)(4(56)(78))', 1, 1, 0, 'i', 4),
FROM DUAL;

 

REGEXP_COUNT 함수

 

REGEXP_COUNT 함수는 source_char에서 일치한 pattern 의 횟수를 반환합니다.

 

REGEXP_COUNT(SOURCE_CHAR, PATTERN, [, POSITION [, MATCH_PARAM]])

 

서브표현식의 반복횟수를 반환합니다.

 

SELECT
-- 5
REGEXP_COUNT('123123123123123', '123', 1)
-- 3
, REGEXP_COUNT('123123123', '123', 1)
FROM DUAL