[오라클] REGEXP_REPLACE 함수에서의 정규표현식(Regular Expression) 사용

정규표현식은 모든 프로그래밍언어에서 특정 다수의 데이터를 처리할 때 유용하게 쓰이지만, 워낙 대치되는 것이 많아 쉽게 사용하기가 어렵다. 하지만 한 번 이해해두면, 찾아보면서 사용할 수 있기 때문에 시간을 많이 아낄 수 있다. 나는 오라클에서 공백이 있을 수도 있고 없을 수도 있는 데이터를 치환하기 위하여 사용하였는데, 꽤나 유용하고 흥미로웠기 때문에 정리해둔다.

 

1️⃣ REGEXP_REPLACE 기본 구조

REGEXP_REPLACE(source_string, pattern, replacement [, position [, occurrence [, match_parameter]]])

파라미터	설명
source_string	정규식을 적용할 원본 문자열
pattern	정규식 패턴
replacement	대체할 문자열
position (optional)	시작 위치 (기본값 1)
occurrence (optional)	몇 번째 매칭을 대체할지 (기본값 0 = 모두)
match_parameter (optional)	'i' (대소문자 무시), 'n' (줄바꿈 포함), 'm' (multiline), 'x' (확장 모드) 등

 

 

2️⃣ 자주 사용하는 정규식 패턴

패턴	의미	예시
.	임의의 한 문자 (줄바꿈 제외)	'a.c' → 'abc', 'a9c'
*	앞의 요소 0회 이상 반복	'a*' → '', 'a', 'aaa'
+	앞의 요소 1회 이상 반복	'a+' → 'a', 'aaa'
?	앞의 요소 0 또는 1회	'a?' → '', 'a'
{m,n}	m~n회 반복	'a{2,3}' → 'aa', 'aaa'
[]	문자 집합	'[abc]' → 'a', 'b', 'c'
[^abc]	제외한 문자	'[^abc]' → 'd', 'e'
`	`	OR 조건
()	그룹	'(ab)+' → 'abab'
\d	숫자 [0-9]
\w	단어 문자 [a-zA-Z0-9_]
\s	공백 문자 (space, tab 등)
\b	단어 경계 (Oracle 12c 이후 지원 제한 있음)

 

🎈예제 1) 숫자만 남기기(전화번호 정리)

SELECT REGEXP_REPLACE('전화번호: 010-1234-5678', '[^\d]', '') AS result
FROM dual;

--결과
01012345678

\d는 숫자 [0-9], [^...]는 부정 (숫자가 아닌 것 제거)

 

🎈예제 2) 공백 제거(모든 띄어쓰기 제거)

SELECT REGEXP_REPLACE('공 백 이 있 는 문 장', '\s', '') AS result
FROM dual;

--결과
공백이있는문장

\s는 공백 문자 (space, tab, newline 포함). \s+로 연속 공백도 제거 가능

 

🎈예제 3) 특수문자 제거(알파벳/숫자만 남기기)

SELECT REGEXP_REPLACE('Hello! My ID is: user_123.', '[^a-zA-Z0-9]', '') AS result
FROM dual;

--결과
HelloMyIDisuser123

알파벳과 숫자가 아닌 모든 문자 제거

 

🎈예제 4) 특정 패턴 형식화(날짜 포맷 변경)

SELECT REGEXP_REPLACE('2025-04-04', '(\d{4})-(\d{2})-(\d{2})', '\3/\2/\1') AS result
FROM dual;

--결과
04/04/2025

그룹을 만들어 순서를 바꾸고 /로 포맷 변경

 

 

3️⃣ Oracle 고유 match parameter

파라미터	의미
'i'	대소문자 무시
'c'	대소문자 구분
'n'	줄바꿈 문자 포함 (.이 줄바꿈 포함하게 됨)
'm'	multiline 모드 (^, $ 가 줄 경계 인식)
'x'	주석 허용 모드 (공백 무시하고 # 이후는 주석)

 

4️⃣ 그룹과 역참조(Backreference)

🔷 그룹 묶기: ()

SELECT REGEXP_REPLACE('abcabc', '(abc)', 'X') FROM dual;
-- 결과: XX

🔷 역참조 (참조해서 치환): \1, \2, ...

SELECT REGEXP_REPLACE('2025-04-04', '(\d{4})-(\d{2})-(\d{2})', '\3/\2/\1') FROM dual;
-- 결과: 04/04/2025

 

 

5️⃣ 특수 문자 이스케이프

정규식에서 의미 있는 특수문자를 문자 그대로 사용하고 싶다면 역슬래시(\)로 escape해야 합니다.

문자	의미	이스케이프 필요
.	아무 문자	\.
*	반복	\*
+	반복	\+
(, ), [, ], {, }	그룹 또는 집합	각각 \(, \), ...
\	역슬래시 자체	\\

※ SQL 문자열에서는 역슬래시도 문자열 내에서 이중 처리해야 할 수 있음 ('\\.' 등)

 

6️⃣ 실용 예시들

🔹 이메일 도메인 마스킹

SELECT REGEXP_REPLACE('test@example.com', '@.*', '@****') FROM dual; -- 결과: test@****

🔹 숫자 아닌 모든 문자 제거

SELECT REGEXP_REPLACE('전화번호: 010-1234-5678', '[^\d]', '') FROM dual; -- 결과: 01012345678

🔹 특정 구간 추출 (REGEXP_SUBSTR과 함께)

SELECT REGEXP_SUBSTR('무상 수리 공장 방문', '무상\s*수리\s*공장') FROM dual;
-- 결과: 무상 수리 공장

 

 

✅ 마무리 요약

정규식 개념	Oracle 지원 여부
기본 정규식 문법 (., *, +, [], ())	✅ 지원
역참조 및 그룹 치환 (\1, \2)	✅ 지원
Lookahead / Lookbehind	❌ Oracle 19c 이하에서 미지원
줄바꿈 포함 매칭	🔸 'n' 옵션 필요
단어 경계 \b	🔸 제한적 지원 (버전 의존)