데이터 정제(Cleaning) 작업을 하다 보면 010-1234-5678 같은 연락처에서 하이픈(-)을 제거하고 숫자만 남기거나, 주소에서 번지수를 제외한 텍스트만 추출해야 하는 경우가 많습니다.
오라클의 REGEXP_REPLACE 함수를 사용하면 복잡한 REPLACE 중첩 없이 한 줄의 쿼리로 해결할 수 있습니다.
1. 기본 함수 구조: REGEXP_REPLACE
정규표현식을 지원하는 치환 함수입니다.
SQL
REGEXP_REPLACE(대상문자열, '패턴', '치환할문자')2. 문자열에서 "숫자만" 추출하기
숫자가 아닌 모든 문자를 찾아 공백('')으로 치환하여 없애버리는 원리입니다.
- 정규식 패턴: [^0-9] 또는 [^[:digit:]]
- ^: 패턴의 시작 부분에서 사용되면 'NOT(부정)'을 의미합니다. 즉, 숫자가 아닌 것들을 의미하죠.
예제 쿼리:
SQL
SELECT
'ABC1234-5678-DEF' AS 원본,
REGEXP_REPLACE('ABC1234-5678-DEF', '[^0-9]', '') AS 숫자만_추출
FROM DUAL;결과: 12345678
3. 문자열에서 "숫자만" 제거하기 (문자만 남기기)
반대로 숫자(0-9)를 모두 찾아 공백으로 치환하여 없앱니다.
- 정규식 패턴: [0-9] 또는 [[:digit:]]
예제 쿼리:
SQL
SELECT
'서울시 강남구 123-45' AS 원본,
REGEXP_REPLACE('서울시 강남구 123-45', '[0-9]', '') AS 숫자_제거
FROM DUAL;결과: 서울시 강남구 - (숫자만 쏙 빠진 상태)
4. 실무 응용: 특수문자까지 깔끔하게 제거하기
숫자와 한글/영문만 남기고 싶을 때는 어떻게 할까요?
숫자와 문자만 남기기 (특수문자 제거)
SQL
SELECT
REGEXP_REPLACE('Hello! 123@#$', '[^0-9a-zA-Z가-힣]', '') AS 결과
FROM DUAL;결과: Hello123
5. 핵심 요약 표
| 목표 | 사용 패턴 | 설명 |
| 숫자만 추출 | [^0-9] | 숫자가 아닌 모든 문자를 제거 |
| 숫자만 제거 | [0-9] | 모든 숫자를 제거 |
| 영문/숫자만 유지 | [^0-9a-zA-Z] | 영문과 숫자가 아닌 나머지를 제거 |
팁: 대소문자 구분 및 성능
- REGEXP_REPLACE는 일반 REPLACE보다 리소스를 더 많이 사용합니다. 데이터 양이 수백만 건 이상으로 매우 많다면 전처리 단계에서 정제하거나 실행 계획을 확인하는 것이 좋습니다.
- 한글의 경우 가-힣 패턴을 사용하면 대부분의 완성형 한글을 필터링할 수 있습니다.