[Database] SQL의 정규표현식: 정의, 핵심 패턴 표, DBMS별 사용법, 이메일·전화번호·주소 예시

1. 정규표현식 개념

• 정의: 문자열 패턴 기술 언어. 텍스트 검색, 검증(밸리데이션), 치환, 추출 목적
• 활용 목적: 입력값 형식 검증, 로그/메시지 패턴 탐지, 코드/키 추출, 데이터 정제·마스킹
• 장점: 간결한 패턴 정의, 복합 규칙 표현력, 다양한 DB 내장 함수 연계
• 주의점: 과도한 복잡도로 인한 가독성 저하, 대용량에서의 성능 이슈, DBMS별 문법 차이

 

2. 자주 쓰는 패턴 요약 표

범주기호/패턴의미예시

앵커	^ / $	문자열 시작 / 끝	^abc$ → 전체가 정확히 abc
수량자	* + ?	0+ / 1+ / 0 또는 1	\d+ 숫자 1글자 이상
수량자(한정)	{m} {m,} {m,n}	정확히 m / m 이상 / m~n	\d{5} → 5자리 숫자
문자클래스	. / [abc] / [^abc]	임의 1글자 / a,b,c 중 1 / 제외	^[A-Za-z]+$
약어클래스	\d \w \s	숫자/단어문자/공백	\w+@\w+
경계	\b / \B	단어 경계 / 비경계	\bcat\b
그룹	( ) / (?: )	캡처 / 비캡처	(abc)+
선택	`	`	OR
이스케이프	\. \-	메타문자 무력화	리터럴 점/하이픈 매칭
전방탐색*	(?=…) / (?!…)	긍정/부정 전방	\d+(?=원)
후방탐색*	(?<=…) / (?<!…)	긍정/부정 후방	(?<=#)\w+

* 전·후방탐색 지원 여부: PostgreSQL/BigQuery/Oracle 지원, MySQL 8.0(디폴트 ICU) 일부 지원, MariaDB/버전에 따라 제약

 

3. DBMS별 정규표현식 함수 비교 표

DBMS매칭(검증)추출치환비고

PostgreSQL	col ~ 're' (대소문자 구분), ~*(무시)	substring(col from 're'), regexp_matches	regexp_replace(col,'re','rep')	Lookaround, 그룹 캡처 강력
MySQL 8+	REGEXP_LIKE(col,'re',[opts])	REGEXP_SUBSTR(col,'re',pos,occ)	REGEXP_REPLACE(col,'re','rep',pos,occ)	opts에 i(대소문자무시) 등
Oracle	REGEXP_LIKE	REGEXP_SUBSTR(subexpr 인자), REGEXP_INSTR	REGEXP_REPLACE	대용량에서도 안정적 구현
BigQuery	REGEXP_CONTAINS(col,'re')	REGEXP_EXTRACT, REGEXP_EXTRACT_ALL	REGEXP_REPLACE	스탠다드 SQL
SQL Server	(네이티브 미지원) LIKE/PATINDEX 대체	(동일)	REPLACE/TRANSLATE	필요 시 CLR, 외부 도구 연계

 

4. 실무 패턴 (검증·추출·치환)

4-1. 이메일 형식 검증/추출

• 실용 패턴(권장 실무형):
  ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$
  (RFC 완전 대응 아님. 대부분 서비스에 충분한 현실적 타협안)

PostgreSQL(검증)

 

SELECT email
FROM t
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

 

MySQL 8+(검증)

 

SELECT email
FROM t
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

 

Oracle(로컬파트/도메인 추출)

 

-- 1=전체, 2=로컬파트, 3=도메인
SELECT
  REGEXP_SUBSTR(email, '^([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,})$', 1, 1, NULL, 1) AS local_part,
  REGEXP_SUBSTR(email, '^([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,})$', 1, 1, NULL, 2) AS domain_part
FROM t
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

BigQuery(도메인 추출)

SELECT REGEXP_EXTRACT(email, '@([A-Za-z0-9.-]+\.[A-Za-z]{2,})') AS domain
FROM `proj.dataset.t`
WHERE REGEXP_CONTAINS(email, r'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

 

4-2. 전화번호 형식(한국/국제) 검증·정규화

• 한국 일반형: ^0\d{1,2}-\d{3,4}-\d{4}$
• 숫자만 입력 허용 후 포맷팅: ^\d{9,12}$ 등으로 1차 검증 후 하이픈 삽입
• 국제형(느슨): ^\+?\d{1,3}[-\s]?\d{1,4}[-\s]?\d{3,4}[-\s]?\d{4}$

PostgreSQL(숫자만 → 010-1234-5678 포맷팅 예시)

 

-- 10자리(02 포함 지역), 11자리(010~) 케이스 분기 예시
SELECT
  CASE
    WHEN phone ~ '^\d{10}$' THEN
      REGEXP_REPLACE(phone, '^(\d{2})(\d{3,4})(\d{4})$', '\1-\2-\3')
    WHEN phone ~ '^\d{11}$' THEN
      REGEXP_REPLACE(phone, '^(\d{3})(\d{4})(\d{4})$', '\1-\2-\3')
    ELSE phone
  END AS phone_fmt
FROM t;

MySQL(국제형 검증)

 

SELECT phone
FROM t
WHERE REGEXP_LIKE(phone, '^\+?\d{1,3}[-\s]?\d{1,4}[-\s]?\d{3,4}[-\s]?\d{4}$');

Oracle(마스킹: 중간 3~4자리 → *)

SELECT REGEXP_REPLACE(phone, '(\d{3})-(\d{3,4})-(\d{4})', '\1-****-\3') AS phone_masked
FROM t;

 

4-3. 주소·우편번호 등 정형 텍스트

• 한국 우편번호(도로명 5자리): ^\d{5}$
• 미국 ZIP/ZIP+4: ^\d{5}(?:-\d{4})?$
• 미국 City, State, ZIP 라인 추출: ^([A-Za-z.\s]+),\s*([A-Z]{2})\s*(\d{5}(?:-\d{4})?)$

Oracle(도시/주/우편번호 캡처 추출)

 

SELECT
  REGEXP_SUBSTR(addr_line, '^([A-Za-z.\s]+),\s*([A-Z]{2})\s*(\d{5}(?:-\d{4})?)$', 1, 1, NULL, 1) AS city,
  REGEXP_SUBSTR(addr_line, '^([A-Za-z.\s]+),\s*([A-Z]{2})\s*(\d{5}(?:-\d{4})?)$', 1, 1, NULL, 2) AS state,
  REGEXP_SUBSTR(addr_line, '^([A-Za-z.\s]+),\s*([A-Z]{2})\s*(\d{5}(?:-\d{4})?)$', 1, 1, NULL, 3) AS zip
FROM t
WHERE REGEXP_LIKE(addr_line, '^([A-Za-z.\s]+),\s*([A-Z]{2})\s*(\d{5}(?:-\d{4})?)$');

PostgreSQL(도로명 우편번호 검증)

 

SELECT zipcode
FROM t
WHERE zipcode ~ '^\d{5}$';

4-4. 데이터 정제·마스킹 공통 패턴

• 이메일 로컬파트 일부 마스킹

 

-- PostgreSQL
SELECT REGEXP_REPLACE(email, '^(.{2})[^@]*(@.*)$', '\1****\2') AS email_masked
FROM t;

• 불필요 문자 제거(숫자만 보존)

 

-- BigQuery
SELECT REGEXP_REPLACE(raw, r'\D', '') AS digits_only
FROM `proj.dataset.t`;

• 특정 접미사 존재 행 필터링

 

-- MySQL
SELECT filename FROM t
WHERE REGEXP_LIKE(filename, '\.(pdf|docx|xlsx)$', 'i');

 

5. 비교 요약

상황권장 DB/함수포인트

간단 검증(이메일/전화)	전 DB: REGEXP_LIKE 또는 ~	단순·가독 패턴 사용, 과학적 RFC 완벽성보다 현실성 우선
대량 치환·정제	PostgreSQL/BigQuery/Oracle	regexp_replace 계열 활용, 배치 전 테스트 필수
세밀 추출(그룹)	Oracle REGEXP_SUBSTR(..., subexpr) / BigQuery REGEXP_EXTRACT	서브그룹 인덱스 활용, 스키마 컬럼 분해 자동화
고급 문법(lookaround)	PostgreSQL/BigQuery/Oracle	MySQL 버전·설정 확인 필요
SQL Server 환경	LIKE/PATINDEX + 앱/ETL 측 정규식	네이티브 정규식 부재, ETL로 오프로딩 권장

 

6. 성능·품질 체크리스트

• 인덱스 고려: 정규식 필터는 인덱스 활용 제한. 가능한 프리픽스 조건(예: email LIKE 'a%') 병행
• 패턴 간소화: 과도한 백트래킹 방지. 수량자 축소, 앵커 사용
• 샘플링 검증: 대표·경계 케이스 포함 테스트 데이터셋 확보
• 국제화: 유니코드 문자 클래스(\p{L} 등) 지원 여부 확인
• 로그·모니터링: 실패 사례 저장, 예외 패턴 주기적 보완