[SQL 개념 정리 15] 정규표현식

정규표현식

SQL에서 정규표현식(Regular Expression, RegEx)은 문자열 데이터를 처리하거나 특정 패턴을 검색, 매칭, 치환하는 데 유용한 기능입니다. SQL의 정규표현식은 데이터베이스 엔진에 따라 지원 방식과 기능이 조금씩 다릅니다.

---

SQL에서 정규표현식을 지원하는 주요 함수

• REGEXP_LIKE(): 특정 패턴과 일치하는지 확인.
• REGEXP_REPLACE(): 특정 패턴을 찾아 치환.
• REGEXP_SUBSTR(): 특정 패턴과 일치하는 하위 문자열 반환.
• REGEXP(): WHERE 절에서 패턴 매칭 (MySQL 8.0 이전).

---

주요 정규표현식 함수

1) REGEXP_LIKE()

문자열이 특정 정규표현식 패턴과 일치하는지 확인합니다.

SELECT column_name
FROM table_name
WHERE REGEXP_LIKE(column_name, 'pattern');

 

예시:

• 이메일 형식 검증

SELECT email
FROM users
WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\\\.[a-zA-Z]{2,}$');

올바른 이메일 형식만 조회합니다.

• 특정 문자열 포함

SELECT name
FROM employees
WHERE REGEXP_LIKE(name, '^(Kim|Lee|Park)');

Kim, Lee, Park으로 시작하는 이름을 조회합니다.

 

2) REGEXP_REPLACE()

정규표현식 패턴과 일치하는 문자열을 다른 문자열로 대체합니다.

SELECT REGEXP_REPLACE(column_name, 'pattern', 'replacement')
FROM table_name;

 

예시:

• 특수문자 제거

SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS sanitized_phone
FROM contacts;

전화번호에서 숫자가 아닌 모든 문자를 제거합니다.

• 공백을 하이픈으로 치환

SELECT REGEXP_REPLACE(address, '\\\\s+', '-') AS formatted_address
FROM locations;

주소에서 모든 공백을 하이픈으로 변환합니다.

3) REGEXP_SUBSTR()

정규표현식 패턴과 일치하는 첫 번째 하위 문자열을 반환합니다.

SELECT REGEXP_SUBSTR(column_name, 'pattern')
FROM table_name;

 

예시:

• 날짜 추출

SELECT REGEXP_SUBSTR(notes, '\\\\d{4}-\\\\d{2}-\\\\d{2}') AS extracted_date
FROM logs;

텍스트에서 날짜 형식(YYYY-MM-DD)을 추출합니다.

• 도메인명 추출

SELECT REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain
FROM users;

이메일에서 도메인 부분(@domain)을 추출합니다.

---

MySQL 정규표현식의 메타문자

메타문자	설명	예시	매칭 결과
.	임의의 단일 문자	a.c	abc, adc
^	문자열의 시작	^abc	abc123 (O), 123abc (X)
$	문자열의 끝	xyz$	123xyz (O), xyz123 (X)
*	0회 이상 반복	ab*	a, ab, abb
+	1회 이상 반복	ab+	ab, abb
?	0회 또는 1회 반복	ab?	a, ab
{n}	정확히 n회 반복	a{3}	aaa
{n,}	최소 n회 이상 반복	a{2,}	aa, aaa, aaaa
{n,m}	최소 n회, 최대 m회 반복	a{2,4}	aa, aaa, aaaa
[]	문자 클래스 (괄호 안의 문자 중 하나와 매칭)	[abc]	a, b, c
[^]	부정 문자 클래스	[^abc]	d, e, f (a, b, c 제외)
()	그룹핑 (서브패턴)	(ab)+	ab, abab, ababab

---

MySQL 정규표현식 예제

1) 특정 패턴 찾기

• 전화번호 형식 찾기

SELECT phone
FROM contacts
WHERE phone REGEXP '^\\\\d{3}-\\\\d{3,4}-\\\\d{4}$';

010-1234-5678와 같은 형식의 전화번호를 찾습니다.

 

2) 데이터 유효성 검증

• 우편번호 검증

SELECT postal_code
FROM addresses
WHERE REGEXP_LIKE(postal_code, '^\\\\d{5}(-\\\\d{4})?$');

5자리 또는 9자리 우편번호를 검증합니다(예: 12345 또는 12345-6789).

 

3) 텍스트 변환

• 숫자만 추출

SELECT REGEXP_REPLACE(input_text, '[^0-9]', '') AS only_numbers
FROM raw_data;

입력 문자열에서 숫자만 남깁니다.

---

정규표현식을 지원하지 않는 SQL 엔진 처리

일부 SQL 엔진은 정규표현식을 지원하지 않습니다(MySQL 8.0 이전 버전 등). 이 경우, 정규표현식을 대신하는 방법:

• LIKE 연산자:
  • 단순한 패턴 매칭에서 사용.

SELECT name
FROM employees
WHERE name LIKE 'A%';

A로 시작하는 이름 조회.

• 문자열 함수 조합:
  • SUBSTRING, CHARINDEX 등을 활용하여 정규표현식을 대신.

---

요약

• 정규표현식 함수: MySQL에서 REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR 등을 사용하여 문자열 패턴 매칭, 변환, 추출이 가능.
• 활용 사례:
  • 데이터 유효성 검증 (예: 이메일, 전화번호 형식 검증)
  • 특정 패턴 추출 (예: 날짜, 도메인 등)
  • 문자열 변환 (예: 공백 제거, 숫자만 추출)
• MySQL에서 정규표현식은 텍스트 데이터를 처리하고 분석하는 강력한 도구입니다. 다양한 메타문자를 조합하여 원하는 패턴을 정의하고 활용할 수 있습니다.

---

SQL에서 정규표현식은 텍스트 데이터를 다룰 때 유연성과 강력함을 제공하는 도구입니다. REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR과 같은 함수와 메타문자를 조합하여 데이터를 검증, 변환, 추출할 수 있는 다양한 방법을 배웠습니다.