[SQL] SQL에서 정규표현식(Regex) 활용 가이드

Hyunjun Kim·2024년 10월 2일

regex regular expression sql 정규표현식

SQL

목록 보기

10/90

이번 글에서는 정규표현식(Regex)의 기본 개념부터 SQL에서 실제로 어떻게
활용되는지, 그리고 대규모 데이터베이스 환경에서의 성능 이슈까지 정리해 보았다.

1. 정규표현식 기초 개념

정규표현식은 문자열에서 특정 패턴을 찾거나 치환할 수 있는 강력한 도구다.

1.1 문자 매칭

문자 그대로 입력: "abc" → 문자열 중 "abc"가 포함된 경우 매칭된다.
OR 매칭 (|): "abc|dr" → "abc" 또는 "dr"을 포함한 문자열을 찾는다.

1.2 문자 그룹

[a-z] → 소문자 알파벳
[A-Z] → 대문자 알파벳
[0-9] → 숫자
[가-힣] → 한글

예시 차이

"abc" : 문자열 "abc" 자체를 찾는다.

[abc] : a 또는 b 또는 c 를 포함한 문자열을 찾는다.

1.3 패턴 반복

* : 0회 이상
+ : 1회 이상
{n,m} : n ~ m회 반복
? : 0 또는 1회

1.4 패턴 위치 제한

^ : 문자열 시작
$ : 문자열 끝
. : 임의의 한 문자

1.5 메타 문자 이스케이프

정규표현식 예약어(*, +, ., (), [], {})를 문자 그대로 쓰고
싶다면 \로 escape 해야 한다.

2. SQL에서 정규표현식 사용

2.1 주요 함수

DBMS 함수 예시

MySQL
REGEXP, REGEXP_LIKE,REGEXP_REPLACE, REGEXP_SUBSTR
Oracle
REGEXP_LIKE, REGEXP_REPLACE,REGEXP_SUBSTR, REGEXP_INSTR
PostgreSQL
~, ~*, !~, !~* (정규식 매칭 연산자),
regexp_replace,regexp_split_to_table

2.2 MySQL 함수별 설명

MySQL 정규표현식 함수 종류 및 설명

함수 이름	설명	사용 예시
REGEXP / RLIKE	정규표현식과 일치하는지 확인	SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$';
REGEXP_ LIKE	정규표현식 일치여부 Boolean 반환	SELECT REGEXPLIKE(email, '^[A-Za-z0-9.%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$') AS is_valid FROM users;
REGEXP_ REPLACE	정규표현식과 일치부분을 대체	SELECT REGEXP_REPLACE(phone_number, '[^0-9]', '') AS clean_phone FROM contacts;
REGEXP_ SUBSTR	정규표현식과 일치부분 추출	SELECT REGEXP_SUBSTR(description, '[A-Za-z]+') AS first_word FROM products;
REGEXP_ INSTR	정규표현식 일치부분의 시작 위치 반환	SELECT REGEXP_INSTR(email, '@') AS at_position FROM users;
REGEXP_COUNT	정규표현식 일치하는 패턴의 개수	SELECT REGEXP_COUNT(description, 'engineer') AS engineer_count FROM jobs;

2.3 MySQL 정규표현식 함수 상세 설명

REGEXP / RLIKE

REGEXP와 RLIKE는 동일한 기능을 제공하고, 문자열이 지정된 정규표현식과 일치하는지 여부를 확인하는 데 사용된다. 주로 WHERE 절에서 조건부 필터링에 사용됨.

-- 이메일 형식이 유효한 사용자 조회
SELECT email
FROM users
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

REGEXP_LIKE

REGEXP_LIKE는 REGEXP와 유사하게 문자열이 정규표현식과 일치하는지 여부를 반환하지만, 함수 형태로 사용됨

-- 전화번호 형식이 유효한지 확인
SELECT phone_number
FROM contacts
WHERE REGEXP_LIKE(phone_number, '^\d{3}-\d{4}-\d{4}$');

REGEXP_REPLACE

REGEXP_REPLACE는 문자열 내에서 정규표현식과 일치하는 부분을 다른 문자열로 대체하는 함수

-- 전화번호에서 비숫자 문자 제거
SELECT REGEXP_REPLACE(phone_number, '[^0-9]', '') AS clean_phone
FROM contacts;

REGEXP_SUBSTR

REGEXP_SUBSTR는 문자열에서 정규표현식과 일치하는 부분 문자열을 추출하는 함수

-- 설명에서 첫 번째 단어 추출
SELECT REGEXP_SUBSTR(description, '[A-Za-z]+') AS first_word
FROM products;

REGEXP_INSTR

REGEXP_INSTR는 문자열 내에서 정규표현식과 일치하는 부분의 시작 위치를 반환하는 함수

-- 이메일에서 '@' 기호의 위치 찾기
SELECT REGEXP_INSTR(email, '@') AS at_position
FROM users;

REGEXP_COUNT

REGEXP_COUNT는 문자열에서 정규표현식과 일치하는 패턴의 개수를 세는 함수

-- 설명에서 'engineer'라는 단어의 개수 세기
SELECT REGEXP_COUNT(description, 'engineer') AS engineer_count
FROM jobs;

3. 실습 예제: 잘못된 IP 주소 찾기

3.1 문제 정의

IPv4 주소는 다음 조건을 만족해야 한다:

옥텟(octet)은 0~255 범위여야 한다.
선행 0이 있으면 안 된다. (예: 192.168.001.1 → 잘못됨)
반드시 4개의 옥텟이 있어야 한다.

3.2 SQL 풀이

WITH filter AS (
    SELECT log_id, ip
    FROM logs
    WHERE NOT REGEXP_LIKE(ip, 
        '^(?:[1-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])
        (?:[.](?:[1-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])){3}$')
)
SELECT ip, COUNT(*) invalid_count
FROM filter
GROUP BY ip
ORDER BY 2 DESC, 1 DESC;

3.3 실행 예시

입력 테이블

log_id	ip	status_code
1	192.168.1.1	200
2	256.1.2.3	404
3	192.168.001.1	200
4	192.168.1.1	200
5	192.168.1	500
6	256.1.2.3	404
7	192.168.001.1	200

출력 결과

ip	invalid_count
256.1.2.3	2
192.168.001.1	2
192.168.1	1

4. 추가 정규표현식 기능

4.1 캡처 그룹 (Capture Group)

(abc) : "abc"라는 그룹을 캡처한다.
SQL에서 REGEXP_SUBSTR와 함께 캡처 그룹을 활용할 수 있다.

예시

SELECT REGEXP_SUBSTR('hello123world', '([0-9]+)', 1, 1) AS first_number;
-- 결과: 123

[0-9]+ 가 캡처 그룹으로 잡혀 "123"이 추출됨.

4.2 비캡처 그룹 (?: )

(?:abc) : 그룹은 묶지만 결과로 캡처하지 않는다.
성능이 조금 더 좋고, 그룹 번호가 증가하지 않는다.

예시

SELECT REGEXP_SUBSTR('abcabcabc', '(?:abc){2}', 1, 1) AS match_str;
-- 결과: abcabc

(abc){2} 로 했다면 그룹 번호가 생기지만, (?:abc){2}는 캡처하지 않고 단순 반복만 체크.

4.3 전방 탐색 (Lookahead)

(?=...) : 패턴 뒤에 특정 조건이 따라와야 함
(?!...) : 패턴 뒤에 특정 조건이 오면 안 됨

예시

SELECT REGEXP_SUBSTR('price100', 'price(?=[0-9]+)') AS matched;
-- 결과: price

price뒤에 숫자가 붙어 있어야 매칭됨.

SELECT REGEXP_SUBSTR('priceABC', 'price(?=[0-9]+)') AS matched;
-- 결과: NULL

price뒤에 숫자가 없으므로 매칭 실패.

4.4 후방 탐색 (Lookbehind)

(?<=...) : 특정 패턴 앞에 올 경우만 매칭
(?<!...) : 특정 패턴 앞에 오지 않는 경우만 매칭

예시

SELECT REGEXP_SUBSTR('USD100', '(?<=USD)[0-9]+') AS amount;
-- 결과: 100

USD 바로 뒤에 오는 숫자만 매칭.

예시

SELECT REGEXP_SUBSTR('EUR200', '(?<=USD)[0-9]+') AS amount;
-- 결과: NULL

USD가 앞에 없으므로 매칭 안 됨.

4.5 단어 경계 (Word Boundary)

\bword\b : 단어 단위로 "word"를 찾음

예시

SELECT REGEXP_SUBSTR('word words sword', '\\bword\\b') AS matched;
-- 결과: word

"word" 는 단독 단어라 매칭되지만, "words", "sword"는 매칭되지 않음.

5. 대규모 데이터베이스에서 성능 고려사항

정규표현식은 유연하지만, 대규모 데이터셋에서는 성능 병목이 될 수 있다.

5.1 성능 이슈

인덱스 미활용: REGEXP 조건은 대부분 인덱스를 타지 않는다.
풀 테이블 스캔 발생 → 수백만 건 이상에서 지연이 크다.

5.2 최적화 전략

가능한 경우 LIKE나 INSTR 같은 단순 연산으로 대체한다.
정규표현식을 반드시 써야 한다면, 사전 필터링으로 범위를 좁힌 뒤
적용한다.
자주 쓰는 정규식 검증은 ETL 단계에서 전처리하여 별도 컬럼에
저장한다.\
(예: "is_valid_ip" 컬럼 추가)
일부 DBMS에서는 REGEXP의 옵티마이저 힌트 또는 정규식 엔진
최적화 기능을 제공한다.

6. 정규표현식 연습 문제

문제 1

사용자 테이블에서 'a'로 시작하는 이메일 주소를 가진 사용자를 조회하세요.

테이블 구조:

users
- user_id: 사용자 ID
- email: 이메일 주소

SELECT email
FROM users
WHERE email REGEXP '^a';

문제 2

연락처 테이블에서 '(123)' 형식의 전화번호를 가진 연락처를 조회하세요.

테이블 구조:

contacts
- contact_id: 연락처 ID
- phone_number: 전화번호

SELECT phone_number
FROM contacts
WHERE phone_number REGEXP '^\\(123\\)';

문제 3

사용자 테이블에서 'example.com' 도메인을 가진 이메일 주소를 조회하세요.

테이블 구조:

users
- user_id: 사용자 ID
- email: 이메일 주소

SELECT email
FROM users
WHERE email REGEXP '@example\\.com$';

문제 4

주문 테이블에서 'YYYY-MM-DD' 형식의 날짜를 가진 주문을 조회하세요.

테이블 구조:

orders
- order_id: 주문 ID
- order_date: 주문 날짜

SELECT order_id, order_date
FROM orders
WHERE order_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

문제 6

제품 설명에서 모든 숫자를 '#' 문자로 대체하여 새로운 열로 표시하세요.

테이블 구조:

products
- product_id: 제품 ID
- description: 제품 설명

SELECT product_id, REGEXP_REPLACE(description, '[0-9]', '#')
       AS updated_description
FROM products;

문제 7

사용자 테이블에서 이메일 주소에서 '@' 이전의 사용자 이름만 추출하여 새로운 열로 표시하세요.

테이블 구조:

users
- user_id: 사용자 ID
- email: 이메일 주소

SELECT user_id,
       REGEXP_SUBSTR(email, '^[^@]+') AS username
FROM users;

문제 8

직원 테이블에서 이메일 주소의 도메인을 'newdomain.com'으로 변경하여 새로운 열로 표시

테이블 구조:

users
- user_id: 사용자 ID
- email: 이메일 주소

SELECT employee_id, email,
       REGEXP_REPLACE(email, '@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$', '@newdomain.com') 
       AS updated_email
FROM employees;

문제 9

사용자 테이블에서 이메일 주소에서 '@' 이전의 사용자 이름만 추출하여 새로운 열로 표시하세요.

테이블 구조:

products
- product_id: 제품 ID
- description: 제품 설명

SELECT product_id,
       description,
       REGEXP_COUNT(description, 'eco') AS eco_count
FROM products;

문제 10

사용자 테이블에서 이메일 주소가 'example.com' 또는 'test.com' 도메인을 가진 사용자만 조회하세요.

테이블 구조:

users
- user_id: 사용자 ID
- email: 이메일 주소

SELECT user_id, email
FROM users
WHERE email REGEXP '(@example\\.com$|@test\\.com$)';

Hyunjun Kim

Data Analytics Engineer 가 되

이전 포스트

[SQL] Mac환경 DBeaver 에 MySQL 연동법

다음 포스트

[SQL] SQL에서 정규표현식(Regex) 활용 가이드

SQL

1. 정규표현식 기초 개념

1.1 문자 매칭

1.2 문자 그룹

1.3 패턴 반복

1.4 패턴 위치 제한

1.5 메타 문자 이스케이프

2. SQL에서 정규표현식 사용

2.1 주요 함수

DBMS 함수 예시

2.2 MySQL 함수별 설명

2.3 MySQL 정규표현식 함수 상세 설명

REGEXP / RLIKE

REGEXP_LIKE

REGEXP_REPLACE

REGEXP_SUBSTR

REGEXP_INSTR

REGEXP_COUNT

3. 실습 예제: 잘못된 IP 주소 찾기

3.1 문제 정의

3.2 SQL 풀이

3.3 실행 예시

입력 테이블

출력 결과

4. 추가 정규표현식 기능

4.1 캡처 그룹 (Capture Group)

예시

4.2 비캡처 그룹 (?: )

예시

4.3 전방 탐색 (Lookahead)

예시

4.4 후방 탐색 (Lookbehind)

예시

예시

4.5 단어 경계 (Word Boundary)

예시

5. 대규모 데이터베이스에서 성능 고려사항

5.1 성능 이슈

5.2 최적화 전략

6. 정규표현식 연습 문제

문제 1

문제 2

문제 3

문제 4

문제 6

문제 7

문제 8

문제 9

문제 10

[SQL] Mac환경 DBeaver 에 MySQL 연동법

[SQL_Q] 조건에 맞는 사용자와 총 거래금액 조회하기 - MySQL

0개의 댓글