[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과 같은 함수와 메타문자를 조합하여 데이터를 검증, 변환, 추출할 수 있는 다양한 방법을 배웠습니다.