Day 2 - 정규 표현식

LIKE 로는 부족할 때

LIKE 의 와일드카드는 %(0자 이상)와 _(정확히 1자) 둘뿐이다. "A 또는 J 로 시작", "같은 글자가 두 번 연달아", "숫자만 3~4자리" 같은 조건은 LIKE 로 쓰기 어렵다. 정규 표현식은 이런 패턴을 한 줄로 적는다.

• Oracle 은 10g 부터 REGEXP_LIKE·REGEXP_SUBSTR·REGEXP_INSTR·REGEXP_REPLACE 를, 11g 부터 REGEXP_COUNT 를 지원한다.
• 패턴 문법은 POSIX 정규 표현식을 따르고, \d·\w·\s 같은 Perl 식 약어도 받는다.

메타문자

• ^ 는 대괄호 밖에서는 시작, 대괄호 안 맨 앞에서는 부정이다. ^[AJ] 와 [^AJ] 는 전혀 다른 뜻이다.
• 반복 기호(*·+·?·{ })는 바로 앞 한 글자, 또는 바로 앞 괄호 묶음에만 붙는다.

문자 클래스

• 대문자 약어는 반대 뜻이다. \D 는 숫자가 아닌 문자, \S 는 공백이 아닌 문자다.
• POSIX 클래스는 대괄호를 두 겹으로 쓴다. 바깥 [ ] 가 집합, 안쪽 [:digit:] 가 클래스 이름이다.

REGEXP_LIKE — WHERE 절의 조건

REGEXP_LIKE(문자열, 패턴 [, 옵션]) 은 참·거짓을 돌려주는 조건이다. SELECT 목록에 값처럼 쓸 수는 없고 WHERE·CASE WHEN 같은 조건 자리에 쓴다.

• 이 노트의 실습 환경인 Oracle 23ai 는 BOOLEAN 형이 생겨 SELECT REGEXP_LIKE(ENAME, 'A') FROM EMP 가 TRUE·FALSE 를 돌려준다. 시험 기준(그 이전 버전)에서는 SELECT 목록에 쓸 수 없다. 이 사이트의 실습장도 시험 기준을 따른다.

SELECT ENAME FROM EMP WHERE REGEXP_LIKE(ENAME, '^[AJ]') ORDER BY ENAME;

ENAME
----------
ADAMS
ALLEN
JAMES
JONES

SELECT ENAME FROM EMP WHERE REGEXP_LIKE(ENAME, '(.)\1') ORDER BY ENAME;

ENAME
----------
ALLEN
MILLER
SCOTT

• (.) 가 아무 글자 하나를 잡고, \1 이 방금 잡은 글자와 같은 글자를 요구한다. ALLEN·MILLER 의 LL, SCOTT 의 TT 가 맞았다.
• 'LL|TT' 로 써도 같은 세 명이 나왔다. 역참조는 어떤 글자인지 몰라도 "같은 글자 두 번"을 찾는다는 점이 다르다.

이름이 정확히 네 글자인 사원이다.

SELECT ENAME FROM EMP WHERE REGEXP_LIKE(ENAME, '^.{4}$') ORDER BY ENAME;

ENAME
----------
FORD
KING
WARD

• ^ 와 $ 를 빼고 '.{4}' 만 쓰면 14명이 모두 나왔다. 다섯 글자 이름 안에도 "연속한 네 글자"가 있기 때문이다.

LIKE 와의 차이

같은 문자 A 를 두 방식으로 찾아 세면 차이가 바로 보인다.

SELECT (SELECT COUNT(*) FROM EMP WHERE ENAME LIKE 'A') AS LIKE_A, (SELECT COUNT(*) FROM EMP WHERE ENAME LIKE '%A%') AS LIKE_PCT, (SELECT COUNT(*) FROM EMP WHERE REGEXP_LIKE(ENAME, 'A')) AS REGEXP_A FROM DUAL;

    LIKE_A   LIKE_PCT   REGEXP_A
---------- ---------- ----------
         0          7          7

• LIKE 'A' 는 이름이 정확히 A 한 글자여야 참이라 0명이다.
• REGEXP_LIKE(ENAME, 'A') 는 LIKE '%A%' 와 같은 뜻이 된다. 둘 다 7명이다.

⚠️ 함정: 정규 표현식의 % 와 _ 는 와일드카드가 아니라 그냥 글자다. 반대로 LIKE 의 . 과 * 도 그냥 글자다. 보기에서 두 문법을 섞어 쓴 패턴은 기대와 다른 결과를 낸다.

REGEXP_SUBSTR · REGEXP_INSTR · REGEXP_COUNT

• 시작 위치와 n번째의 기본값은 1 이다.
• REGEXP_INSTR 의 반환 옵션이 0(기본)이면 일치의 시작 위치, 1 이면 일치가 끝난 다음 위치다.

30번 부서 사원 이름에서 모음을 찾는다.

SELECT ENAME, REGEXP_SUBSTR(ENAME, '[AEIOU]') AS V1, REGEXP_SUBSTR(ENAME, '[AEIOU]', 1, 2) AS V2, REGEXP_INSTR(ENAME, '[AEIOU]') AS P1, REGEXP_COUNT(ENAME, '[AEIOU]') AS CNT FROM EMP WHERE DEPTNO = 30 ORDER BY ENAME;

ENAME      V1                                       V2                                               P1        CNT
---------- ---------------------------------------- ---------------------------------------- ---------- ----------
ALLEN      A                                        E                                                 1          2
BLAKE      A                                        E                                                 3          2
JAMES      A                                        E                                                 2          2
MARTIN     A                                        I                                                 2          2
TURNER     U                                        E                                                 2          2
WARD       A                                                                                          2          1

• WARD 에는 모음이 A 하나뿐이라 두 번째 모음 V2 가 NULL 이다.
• BLAKE 의 첫 모음 A 는 세 번째 글자라 P1 이 3 이다.

문자열 리터럴로 함수의 세부 동작을 확인한다.

SELECT REGEXP_SUBSTR('02-1234-5678', '[0-9]+', 1, 2) AS A, REGEXP_SUBSTR('02-1234-5678', '\d+$') AS B, REGEXP_COUNT('02-1234-5678', '[[:digit:]]') AS C FROM DUAL;

A    B             C
---- ---- ----------
1234 5678         10

• [0-9]+ 는 숫자가 이어진 덩어리 하나다. 두 번째 덩어리가 1234 다.
• \d+$ 는 끝에 붙은 숫자 덩어리다. [[:digit:]] 은 숫자 한 글자라 10번 센다.

SELECT REGEXP_COUNT('banana', 'a') AS A, REGEXP_COUNT('banana', 'ana') AS B, REGEXP_INSTR('ABCABC', 'B', 1, 2) AS C, REGEXP_INSTR('ABCABC', 'B', 1, 1, 1) AS D FROM DUAL;

         A          B          C          D
---------- ---------- ---------- ----------
         3          1          5          3

• banana 에 ana 는 2-4번, 4-6번 자리에 겹쳐 있지만 1 이다. 한 번 일치한 글자 뒤에서 다음 검색을 시작하기 때문이다.
• 두 번째 B 는 5번째 글자다. 반환 옵션 1 을 주면 첫 B(2번) 다음 위치인 3 을 돌려준다.

그룹 번호를 주면 괄호로 묶은 부분만 꺼낸다.

SELECT REGEXP_SUBSTR('user@mail.com', '[^@]+') AS ID, REGEXP_SUBSTR('user@mail.com', '@(.+)$', 1, 1, NULL, 1) AS DOM FROM DUAL;

ID   DOM
---- --------
user mail.com

• [^@]+ 는 @ 가 아닌 글자의 덩어리라 앞쪽 user 가 나온다.
• 여섯 번째 인자 1 은 첫 번째 괄호 그룹을 뜻한다. 그룹 번호가 없으면 @ 까지 포함한 @mail.com 이 나온다.

REGEXP_REPLACE 와 역참조

REGEXP_REPLACE(문자열, 패턴, 바꿀 문자열) 은 일치하는 부분을 모두 바꾼다. 바꿀 문자열을 생략하면 일치한 부분을 지운다.

SELECT ENAME, SUBSTR(REGEXP_REPLACE(ENAME, '[AEIOU]', '*'), 1, 10) AS MASKED FROM EMP WHERE DEPTNO = 10 ORDER BY ENAME;

ENAME      MASKED
---------- ----------------------------------------
CLARK      CL*RK
KING       K*NG
MILLER     M*LL*R

• SUBSTR 는 열 폭을 줄이려고 붙였다. 바뀐 내용은 그대로다.
• MILLER 의 I 와 E 가 모두 바뀌었다. REGEXP_REPLACE 는 기본으로 모든 일치를 바꾼다.

바꿀 문자열 안의 \1, \2, \3 은 패턴에서 괄호로 잡은 부분이다. 날짜 순서를 뒤집고, 전화번호 가운데를 가린다.

SELECT REGEXP_REPLACE('2026-11-03', '(\d{4})-(\d{2})-(\d{2})', '\3/\2/\1') AS D, REGEXP_REPLACE('010-1234-5678', '(\d{3})-(\d{4})-(\d{4})', '\1-****-\3') AS P, REGEXP_REPLACE('a1b2c3', '\D') AS N FROM DUAL;

D          P             N
---------- ------------- ---
03/11/2026 010-****-5678 123

• 그룹 번호는 여는 괄호가 나온 순서다. 연도가 \1, 월이 \2, 일이 \3 이다.
• \D 는 숫자가 아닌 글자다. 바꿀 문자열이 없으니 지워지고 숫자만 남는다. [^[:digit:]] 로 써도 결과가 같았다.

일치 옵션

EMP 의 이름은 모두 대문자라, 소문자 a 로 찾으면 옵션이 결과를 가른다.

SELECT (SELECT COUNT(*) FROM EMP WHERE REGEXP_LIKE(ENAME, 'a')) AS C_DEFAULT, (SELECT COUNT(*) FROM EMP WHERE REGEXP_LIKE(ENAME, 'a', 'i')) AS C_I, (SELECT COUNT(*) FROM EMP WHERE REGEXP_LIKE(ENAME, 'a', 'ic')) AS C_IC FROM DUAL;

 C_DEFAULT        C_I       C_IC
---------- ---------- ----------
         0          7          0

• 기본은 대소문자 구분이라 0명이다. 'i' 를 주면 7명이다.
• 'ic' 처럼 서로 반대인 옵션을 함께 주면 마지막 c 가 적용되어 다시 0명이다. 'ci' 로 순서를 바꾸면 7명이었다.

'n' 과 'm' 은 줄바꿈이 든 문자열에서 차이가 난다. CHR(10) 이 줄바꿈 문자다.

SELECT REGEXP_COUNT('ab' || CHR(10) || 'ac', '^a') AS NO_M, REGEXP_COUNT('ab' || CHR(10) || 'ac', '^a', 1, 'm') AS M, CASE WHEN REGEXP_LIKE('a' || CHR(10) || 'b', 'a.b') THEN 'Y' ELSE 'N' END AS NO_N, CASE WHEN REGEXP_LIKE('a' || CHR(10) || 'b', 'a.b', 'n') THEN 'Y' ELSE 'N' END AS N FROM DUAL;

      NO_M          M N N
---------- ---------- - -
         1          2 N Y

• 기본에서 ^ 는 문자열 맨 앞 하나뿐이라 1 이다. 'm' 이면 둘째 줄 시작도 맞아 2 다.
• 세 번째 열이 NO_N, 네 번째 열이 N 이다. SQL*Plus 가 값 폭(1자)에 맞춰 열 머리를 잘랐다.
• 기본에서 . 은 줄바꿈과 맞지 않아 a.b 가 거짓이다. 'n' 을 주면 참이 된다.

내일은 테이블의 행을 넣고 고치고 지우는 DML(INSERT·UPDATE·DELETE·MERGE)을 다룬다.

📖 용어

• 정규 표현식 : 문자열의 모양을 기호로 적은 패턴. 반복·선택·위치 조건을 한 줄로 표현한다.
• 메타문자 : .·*·^·$ 처럼 글자 그대로가 아니라 특별한 뜻으로 쓰이는 기호.
• 문자 집합 : [ ] 안에 적은 글자 중 하나와 일치하는 표현. [^ ] 는 그 글자들을 뺀 나머지다.
• POSIX 문자 클래스 : [[:digit:]]·[[:alpha:]] 처럼 이름으로 부르는 글자 묶음.
• 그룹 : 괄호로 묶은 패턴의 한 부분. 반복을 함께 걸거나 나중에 다시 가리키는 데 쓴다.
• 역참조 : \1·\2 로 앞에서 괄호로 잡은 문자열을 다시 가리키는 것.
• 일치 옵션 : 'i'·'c'·'n'·'m' 처럼 대소문자·줄바꿈 처리 방식을 바꾸는 인자.