SQL 정규 표현식 함수
IT 위키
더 많은 작업
- 상위 문서: SQL
- SQL Regular Expression Functions
- 정규 표현식 패턴으로 문자열을 검사·검색·추출·치환하는 SQL 함수와 조건
LIKE 연산자는 %와 _ 두 가지 와일드카드만 쓸 수 있어 "숫자로만 이루어졌는가", "두 번째 단어는 무엇인가" 같은 조건을 표현하기 어렵다. Oracle은 10g부터 POSIX 정규 표현식 기반의 REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR를, 11g부터 REGEXP_COUNT를 제공한다. 정규 표현식 자체의 일반 개념은 정규 표현식 문서를 참고하고, 이 문서는 Oracle 함수의 인수와 결과를 다룬다. 결과는 모두 Oracle에서 실행한 것이다.
| 함수 | 형식 | 돌려주는 값 |
|---|---|---|
| REGEXP_LIKE | REGEXP_LIKE(문자열, 패턴 [, 매치옵션]) | 조건(참·거짓). WHERE 절, CASE 조건 등에 쓴다. |
| REGEXP_REPLACE | REGEXP_REPLACE(문자열, 패턴 [, 바꿀문자열 [, 시작위치 [, 발생순번 [, 매치옵션]]]]) | 패턴과 일치한 부분을 바꾼 문자열. 바꿀문자열을 생략하면 일치한 부분을 지운다. |
| REGEXP_SUBSTR | REGEXP_SUBSTR(문자열, 패턴 [, 시작위치 [, 발생순번 [, 매치옵션 [, 하위표현식]]]]) | 일치한 부분 문자열. 없으면 NULL |
| REGEXP_INSTR | REGEXP_INSTR(문자열, 패턴 [, 시작위치 [, 발생순번 [, 반환옵션 [, 매치옵션 [, 하위표현식]]]]]) | 일치한 위치. 없으면 0 |
| REGEXP_COUNT | REGEXP_COUNT(문자열, 패턴 [, 시작위치 [, 매치옵션]]) | 일치한 횟수 |
| 인수 | 기본값 | 의미 |
|---|---|---|
| 시작위치(position) | 1 | 검색을 시작할 문자 위치 |
| 발생순번(occurrence) | 1 (REGEXP_REPLACE는 0) | 몇 번째 일치를 대상으로 할지. REGEXP_REPLACE의 0은 모든 일치를 바꾼다는 뜻이다. |
| 반환옵션(return_option) | 0 | REGEXP_INSTR 전용. 0이면 일치 시작 위치, 1이면 일치 바로 다음 위치 |
| 하위표현식(subexpr) | 0 | 패턴의 몇 번째 괄호 그룹을 대상으로 할지. 0은 일치 전체 |
| 매치옵션(match_param) | 없음 | 아래 표의 문자를 조합한 문자열 |
| 매치옵션 | 의미 |
|---|---|
| 'i' | 대소문자를 구분하지 않는다. |
| 'c' | 대소문자를 구분한다. 생략 시 기본 동작은 NLS_SORT 설정을 따르며 보통은 구분한다. |
| 'n' | 마침표(.)가 줄바꿈 문자에도 일치한다. 기본은 줄바꿈에 일치하지 않는다. |
| 'm' | 여러 줄 모드. ^와 $가 문자열 전체의 시작·끝이 아니라 각 줄의 시작·끝에 일치한다. |
| 'x' | 패턴 안의 공백 문자를 무시한다. |
- 'i'와 'c'처럼 서로 충돌하는 옵션을 함께 주면 마지막 것이 적용된다.
REGEXP_COUNT('Abc', 'abc', 1, 'ic')는 0,'ci'로 주면 1이다. - 'a', CHR(10), 'b'로 된 두 줄 문자열에서
REGEXP_COUNT(s, '^.$')는 0이지만 'm'을 주면 2다.REGEXP_COUNT(s, 'a.b')는 0이지만 'n'을 주면 1이다. - 19c까지 REGEXP_LIKE는 조건이므로 SELECT 목록에 값처럼 쓸 수 없다. 23ai부터는 BOOLEAN 타입이 생겨 SELECT 목록에서도 TRUE·FALSE를 돌려준다.
| 기호 | 의미 | 예와 결과 |
|---|---|---|
| . | 임의의 한 문자(기본적으로 줄바꿈 제외) | a.c는 abc, a1c에 일치
|
| ^ | 문자열의 시작 | ^01은 01로 시작하는 문자열
|
| $ | 문자열의 끝 | com$은 com으로 끝나는 문자열
|
| [ ] | 괄호 안 문자 중 하나 | [0-9]는 숫자 한 개, [.]는 마침표 그 자체
|
| [^ ] | 괄호 안 문자를 제외한 한 문자 | [^0-9]는 숫자가 아닌 문자 한 개
|
| |
또는(OR) | cat|dog
|
| ( ) | 그룹(하위 표현식). 수량자를 묶어 적용하고, 역참조·subexpr 인수의 대상이 된다. | (ab)+는 ab, abab
|
| * | 앞 요소 0회 이상 | ab*는 a, ab, abb
|
| + | 앞 요소 1회 이상 | ab+는 ab, abb
|
| ? | 앞 요소 0회 또는 1회 | colou?r는 color, colour
|
| {m} | 정확히 m회 | [0-9]{4}
|
| {m,} | m회 이상 | {2,}는 공백 2개 이상
|
| {m,n} | m회 이상 n회 이하 | \d{3,4}
|
| \n (n은 1~9) | 역참조. n번째 그룹이 실제로 일치한 문자열을 다시 가리킨다. | ^(abc)\1$는 abcabc에 일치
|
| \ | 메타문자를 일반 문자로 만든다. | \.은 마침표 그 자체
|
[[:digit:]] 등 |
POSIX 문자 클래스. alpha, digit, alnum, space, upper, lower, punct 등 | [[:upper:]]는 대문자 한 개
|
| Perl 확장 | 의미 | 같은 표현 |
|---|---|---|
| \d / \D | 숫자 / 숫자가 아닌 문자 | [0-9] / [^0-9]
|
| \w / \W | 단어 문자(문자·숫자·밑줄) / 그 외 문자 | [[:alnum:]_] / 그 반대
|
| \s / \S | 공백 문자(공백, 탭, 줄바꿈 등) / 공백이 아닌 문자 | [[:space:]] / 그 반대
|
| *? +? ?? {m,n}? | 비탐욕적(최소) 수량자 | 가능한 한 짧게 일치 |
- 기본 수량자 *, +, ?, {m,n}은 탐욕적(greedy)이어서 가능한 한 길게 일치한다. 뒤에 ?를 붙이면 비탐욕적(lazy)이 되어 가능한 한 짧게 일치한다.
[[:alpha:]]와 \w는 한글도 문자로 본다(유니코드 데이터베이스 기준).REGEXP_REPLACE('Ab1 가', '[[:alpha:]]', '*')의 결과는**1 *이고,REGEXP_REPLACE('a_1 가!', '\w', '*')의 결과는*** *!이다.
고객
| 고객번호 | 이메일 | 전화 | 메모 |
|---|---|---|---|
| 1 | [email protected] | 010-1234-5678 | VIP 고객
|
| 2 | [email protected] | 02-123-4567 | 전화 주문 선호
|
| 3 | park#xyz.com | 010-12-3456 | 이메일 오류 |
| 4 | [email protected] | 01098765432 | NULL |
SELECT 고객번호, 이메일
FROM 고객
WHERE REGEXP_LIKE(이메일, '^[A-Za-z0-9._]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
-- 결과: 1, 2, 4 (3은 @가 없어 제외)
SELECT 고객번호,
REGEXP_SUBSTR(이메일, '@(.+)$', 1, 1, NULL, 1) AS 도메인
FROM 고객;
| 고객번호 | 도메인 |
|---|---|
| 1 | abc.co.kr |
| 2 | Mail.com |
| 3 | NULL |
| 4 | test.org |
- 여섯 번째 인수 1은 첫 번째 괄호 그룹
(.+)만 돌려주라는 뜻이다. 0이나 생략이면 @를 포함한@abc.co.kr가 나온다. - 일치하는 부분이 없으면 REGEXP_SUBSTR는 NULL을 돌려준다.
SELECT 고객번호, 전화, REGEXP_REPLACE(전화, '[^0-9]', '') AS 숫자만
FROM 고객;
SELECT 고객번호, 전화
FROM 고객
WHERE REGEXP_LIKE(전화, '^01[016789]-\d{3,4}-\d{4}$');
-- 결과: 1 (010-1234-5678) 한 행
SELECT REGEXP_REPLACE('01098765432', '(\d{3})(\d{4})(\d{4})', '\1-\2-\3') AS 형식
FROM DUAL;
-- 결과: 010-9876-5432
| 고객번호 | 전화 | 숫자만 |
|---|---|---|
| 1 | 010-1234-5678 | 01012345678 |
| 2 | 02-123-4567 | 021234567 |
| 3 | 010-12-3456 | 010123456 |
| 4 | 01098765432 | 01098765432 |
[^0-9]대신\D를 써도 같다.- 형식 검사에서 2번은 01로 시작하지 않고, 3번은 가운데가 2자리, 4번은 하이픈이 없어 제외된다. ^와 $가 없으면 문자열 일부만 일치해도 참이 되므로 전체 형식 검사에는 반드시 붙인다.
- 바꿀 문자열의 \1, \2, \3은 각 괄호 그룹이 일치한 문자열이다.
SELECT 메모, LENGTH(메모) AS 길이,
REGEXP_REPLACE(메모, ' {2,}', ' ') AS 정리,
LENGTH(REGEXP_REPLACE(메모, ' {2,}', ' ')) AS 정리길이
FROM 고객
WHERE 고객번호 IN (1, 2);
| 메모 | 길이 | 정리 | 정리길이 |
|---|---|---|---|
VIP 고객 (공백 3개) |
8 | VIP 고객 | 6 |
전화 주문 선호 (공백 2개씩) |
10 | 전화 주문 선호 | 8 |
- 탭과 줄바꿈까지 포함하려면
'\s+'나'[[:space:]]+'를 쓴다.
SELECT REGEXP_SUBSTR('A-B-C-D', '[^-]+', 1, 3) FROM DUAL; -- C
SELECT REGEXP_SUBSTR('A-B-C-D', '[^-]+', 3, 2) FROM DUAL; -- C (3번째 문자 B부터 B, C, …의 2번째)
SELECT REGEXP_SUBSTR('A-B-C-D', '[^-]+', 1, 5) FROM DUAL; -- NULL (5번째 항목 없음)
SELECT REGEXP_SUBSTR('A,B,,D', '[^,]+', 1, 3) FROM DUAL; -- D (빈 항목은 건너뜀)
SELECT REGEXP_SUBSTR('abc123def45', '[0-9]{2}', 1, 2) FROM DUAL; -- 45 (12 다음 일치는 3이 아니라 45)
[^-]+는 하이픈이 아닌 문자가 1개 이상 이어진 덩어리, 즉 구분자로 나눈 항목이다. 발생순번으로 n번째 항목을 꺼낼 수 있다.[^,]+는 빈 문자열에 일치하지 않으므로, 'A,B,,D'의 세 번째 일치는 빈 항목이 아니라 D다.- 일치는 겹치지 않게 이어서 찾는다. '123'에서 12를 찾은 뒤에는 3부터 다시 찾으므로 '23'은 일치로 세지 않는다.
SELECT REGEXP_SUBSTR('<b>굵게</b><i>기울임</i>', '<.+>') AS 탐욕,
REGEXP_SUBSTR('<b>굵게</b><i>기울임</i>', '<.+?>') AS 비탐욕
FROM DUAL;
SELECT REGEXP_SUBSTR('aaaa', 'a+') AS G,
REGEXP_SUBSTR('aaaa', 'a+?') AS L,
REGEXP_SUBSTR('aaaa', 'a*?') AS L0,
REGEXP_SUBSTR('aaaa', 'a{2,3}') AS M
FROM DUAL;
| 탐욕 | 비탐욕 |
|---|---|
<b>굵게</b><i>기울임</i> |
<b>
|
| G | L | L0 | M |
|---|---|---|---|
| aaaa | a | NULL | aaa |
<.+>는 첫 <부터 마지막 >까지 최대한 길게 잡고,<.+?>는 첫 >에서 멈춘다.a*?는 0회 일치(빈 문자열)가 가장 짧으므로 빈 문자열을 돌려주고, Oracle은 빈 문자열을 NULL로 취급하므로 결과가 NULL이다.a{2,3}은 탐욕적이므로 2개가 아닌 3개를 잡는다.
SELECT REGEXP_COUNT('banana', 'an') AS C1, -- 2
REGEXP_COUNT('banana', 'ana') AS C2, -- 1 (겹치는 일치는 세지 않음)
REGEXP_COUNT('banana', 'a', 3) AS C3, -- 2 (3번째 문자부터 셈)
REGEXP_COUNT('Banana', 'b', 1, 'i') AS C4 -- 1
FROM DUAL;
SELECT REGEXP_INSTR('banana', 'an') AS I1, -- 2
REGEXP_INSTR('banana', 'an', 1, 2) AS I2, -- 4 (두 번째 an의 시작)
REGEXP_INSTR('banana', 'an', 1, 2, 1) AS I3, -- 6 (두 번째 an 바로 다음 위치)
REGEXP_INSTR('banana', 'x') AS I4 -- 0
FROM DUAL;
SELECT REGEXP_REPLACE('banana', 'a', '*') AS R1, -- b*n*n* (발생순번 0: 전부)
REGEXP_REPLACE('banana', 'a', '*', 1, 2) AS R2, -- ban*na (두 번째 a만)
REGEXP_REPLACE('banana', 'a', '*', 3) AS R3 -- ban*n* (3번째 문자부터 전부)
FROM DUAL;
- 'banana'에서 'ana'는 2번째 문자부터 한 번 일치한 뒤 5번째 문자 n부터 다시 찾으므로 1회다. 겹쳐 세면 2회지만 정규 표현식 함수는 겹쳐 세지 않는다.
- 구분자 개수로 항목 수를 셀 때
REGEXP_COUNT('A,B,C', ',') + 1= 3처럼 쓴다.
SELECT REGEXP_SUBSTR('2026-10-08', '(\d{4})-(\d{2})-(\d{2})', 1, 1, NULL, 2) AS 월 -- 10
FROM DUAL;
SELECT REGEXP_INSTR('2026-10-08', '(\d{4})-(\d{2})-(\d{2})', 1, 1, 0, NULL, 3) AS 일위치 -- 9
FROM DUAL;
SELECT REGEXP_REPLACE('홍길동 이몽룡', '(\S+) (\S+)', '\2 \1') AS 바꿈 -- 이몽룡 홍길동
FROM DUAL;
SELECT REGEXP_REPLACE('aa bbb cc', '(.)\1', '#') AS R -- # #b #
FROM DUAL;
(.)\1은 같은 문자가 두 번 연속된 부분이다. 'bbb'에서는 앞의 bb만 바뀌고 남은 b는 짝이 없어 그대로 남는다.
- REGEXP_SUBSTR의 발생순번, 시작위치 인수에 따른 결과와, 일치가 없을 때 NULL(REGEXP_INSTR는 0)을 돌려준다는 점
- 탐욕적 수량자(.+, .*)와 비탐욕적 수량자(.+?, .*?)의 결과 차이
- REGEXP_COUNT는 겹치는 일치를 세지 않는다('banana'에서 'ana'는 1회).
- REGEXP_REPLACE의 발생순번 기본값은 0(전부 치환)이고, 바꿀 문자열을 생략하면 일치한 부분이 삭제된다.
- ^, $가 없으면 부분 일치만으로 REGEXP_LIKE가 참이 된다. 'i' 옵션은 대소문자 무시, \d·\w·\s는 숫자·단어 문자·공백이다.