[SQL] 정규표현식

MySQL의 정규표현식(REGEXP/REGULAR EXPRESSION)

1. 기본 문법과 연산자

sql
Copy
-- REGEXP 또는 RLIKE 사용 가능 (동일한 기능)
SELECT * FROM table_name WHERE column REGEXP 'pattern';
SELECT * FROM table_name WHERE column RLIKE 'pattern';

-- NOT REGEXP로 부정 가능
SELECT * FROM table_name WHERE column NOT REGEXP 'pattern';

1. 주요 패턴 문자

• ^ : 문자열 시작
• $ : 문자열 끝
• . : 임의의 한 문자
• `` : 앞 문자가 0회 이상 반복
• + : 앞 문자가 1회 이상 반복
• ? : 앞 문자가 0 또는 1회 발생
• | : OR 연산자
• [] : 문자 집합
• [^] : 부정 문자 집합
• {} : 반복 횟수 지정

1. 실제 예제

sql
Copy
-- 1. 특정 문자로 시작하는 데이터 검색
SELECT * FROM users WHERE name REGEXP '^김';-- '김'으로 시작하는 이름-- 2. 특정 문자로 끝나는 데이터 검색
SELECT * FROM users WHERE name REGEXP '동$';-- '동'으로 끝나는 이름-- 3. 여러 패턴 중 하나와 일치 (OR 조건)
SELECT * FROM users WHERE name REGEXP '김|이|박';-- '김' 또는 '이' 또는 '박'이 포함된 이름-- 4. 특정 문자 범위 검색
SELECT * FROM products WHERE code REGEXP '[0-9]{3}';-- 3자리 숫자 포함
SELECT * FROM users WHERE name REGEXP '[가-힣]{3}';-- 3글자 한글 이름-- 5. 이메일 형식 검증
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9]+@[a-zA-Z0-9]+\\.[a-zA-Z]+$';

-- 6. 전화번호 형식 검증 (010-XXXX-XXXX)
SELECT * FROM users WHERE phone REGEXP '^010-[0-9]{4}-[0-9]{4}$';

-- 7. 특정 문자를 포함하지 않는 데이터
SELECT * FROM products WHERE name REGEXP '[^0-9]+';-- 숫자를 포함하지 않는 상품명-- 8. 반복 패턴
SELECT * FROM codes WHERE value REGEXP 'A{2,4}';-- A가 2~4번 반복되는 값

1. 자주 사용되는 패턴 예시

sql
Copy
-- 숫자만 포함
^[0-9]+$

-- 영문자만 포함
^[a-zA-Z]+$

-- 한글만 포함
^[가-힣]+$

-- 영문자와 숫자 조합
^[a-zA-Z0-9]+$

-- URL 패턴
^(https?:\\/\\/)?([\\da-z\\.-]+)\\.([a-z\\.]{2,6})([\\/\\w \\.-]*)*\\/?$

-- IP 주소 패턴
^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$

1. 주의사항
2. 대소문자 구분

• MySQL의 REGEXP는 기본적으로 대소문자를 구분하지 않습니다.
• 대소문자를 구분하려면 BINARY 키워드를 사용해야 합니다:

sql
Copy
SELECT * FROM table_name WHERE column REGEXP BINARY 'pattern';

1. 성능

• REGEXP는 LIKE보다 더 복잡한 패턴 매칭이 가능하지만, 성능면에서는 더 느릴 수 있습니다.
• 단순한 패턴 매칭의 경우 LIKE를 사용하는 것이 좋습니다.

1. 이스케이프

• 특수문자를 검색할 때는 백슬래시(\\)로 이스케이프 해야 합니다:

sql
Copy
SELECT * FROM table_name WHERE column REGEXP '\\\\[';-- '[' 문자 검색

1. 실용적인 활용 예제

sql
Copy
-- 1. 올바른 비밀번호 형식 검증 (영문, 숫자, 특수문자 조합, 8자 이상)
SELECT * FROM users
WHERE password REGEXP '^(?=.*[A-Za-z])(?=.*\\\\d)(?=.*[$@$!%*#?&])[A-Za-z\\\\d$@$!%*#?&]{8,}$';

-- 2. 주민등록번호 형식 검증
SELECT * FROM users
WHERE jumin REGEXP '^[0-9]{6}-[1-4][0-9]{6}$';

-- 3. 올바른 파일 확장자 검증
SELECT * FROM files
WHERE filename REGEXP '\\\\.(jpg|jpeg|png|gif)$';

-- 4. 게시글에서 해시태그 추출
SELECT post_content,
    (post_content REGEXP '#[a-zA-Z0-9가-힣]+') as has_hashtag
FROM posts;

-- 5. 잘못된 데이터 찾기
SELECT * FROM products
WHERE product_code NOT REGEXP '^[A-Z]{2}[0-9]{4}$';

이러한 정규표현식을 활용하면 데이터 검증, 필터링, 추출 등 다양한 작업을 효과적으로 수행할 수 있습니다. 실제 사용 시에는 데이터의 특성과 패턴에 맞게 정규표현식을 적절히 수정하여 사용하시면 됩니다.

예제

이메일 조건 검증 (이름@메인도메인.서브도메인):

1. 이름: 1~15자 이내의 영어대소문자 및 숫자
2. 메인도메인: 1~15자 이내의 영어

SELECT
    ID,
    EMAIL
FROM
    테이블
WHERE
    EMAIL REGEXP '^[a-zA-Z0-9]{1,15}@[a-zA-Z0-9]{1,15}\\\\\\\\.com$'

예제

ID, EMAIL이 주어지는데

그 중에서

EMAIL이 다음 조건을 만족하는 것들의 ID, EMAIL을 ID 오름차순으로 출력

EMAIL은
`이름`@`메인도메인`서브도메인

의 형태를 가지며

1. 이름이 1~15자 이내의 영어대소문자 및 숫자로만 이루어진 문자열
2. 메인도메인이 1~15자 이내의 영어

 

풀이

SELECT
	ID,
	EMAIL
FROM
	테이블
WHERE
	EMAIL REGEXP '^[a-zA-Z0-9]{1,15}@[a-zA-Z0-9]{1,15}\.com$'