[MySQL] 정규표현식 검색하기 REGEXP, LIKE

MySQL 정규표현식 활용하여 데이터 검색하기

SQL에서 특정 문자열 조건을 가진 데이터를 검색해야 하는 경우가 종종 있습니다.

 

like는 보다 복잡하고 다양한 문자열 패턴을 검색할 때 쿼리가 상당히 지저분해지는 단점이 있기에

이를 보완할 수 있는 REGEXP 정규표현식에 대해 알아보겠습니다.

---

정규표현식이란?

- 특정한 규칙을 가진 문자열의 집합을 표현하는데 사용하는 형식 언어

- 문자열을 처리하는 방법 중의 하나로, 특정한 조건의 문자를 ‘검색’하거나 ‘치환’하는 과정을 매우 간편하게 처리할 수    있도록 해주는 수단

- SQL부터 스크립트 언어까지 다양한 곳에서 활용될 수 있으며 Pattern을 사용해서 문자열을 처리

- 찾고자 하는 대상에서 정규표현식을 사용해 해당 Pattern과 일치하는 문자열 검색

 

자주 사용하는 패턴 방식

▣ 매칭 (Matching)

패턴	기능	사용 예시	설명
.	문자 하나	"..."	문자열의 길이가 세 글자 이상인 것을 찾음.
|	또는 (OR). |로 구분된 문자에 해당하는 문자열을 찾음.	"데이터|데이타"	‘데이터’ 또는 ‘데이타’에 해당하는 문자열을 찾음.
[]	[] 안에 나열된 패턴에 해당하는 문자열을 찾음.	"[123]d"	대상 문자열에서 ‘1d’ 또는 ‘2d’ 또는 ‘3d’인 문자열을 찾음.
^	시작하는 문자열을 찾음.	"^시작"	대상 문자열에서 ‘시작’으로 시작하는 문자열을 찾음.
$	끝나는 문자열을 찾음.	"끝$"	대상 문자열에서 ‘끝’으로 끝나는 문자열을 찾음.

 

▣ 횟수 (times)

패턴	기능	사용 예시	설명
*	0회 이상 나타나는 문자	"a*"	‘a’가 0번 이상 등장하는 문자열을 찾음. ‘b’, ‘a’, ‘aa’ 모두 해당.
+	1회 이상 나타나는 문자	"찌개+"	‘국’이 1번 이상 등장하는 문자열을 찾음. ‘김치찌개’, ‘된장찌개’, ‘부대찌개’ 모두 해당.
{m,n}	m회 이상 n회 이하 나타나는 문자	"치{1,2}"	‘치’가 1회 이상 2회 이하 등장하는 문자열을 찾음. ‘치커리’, ‘치카치카’ 모두 해당.
?	0~1회 나타나는 문자	"[가나다]?"	‘가’ 또는 ‘나’ 또는 ‘다’가 0~1회 등장하는 문자열을 찾음. ‘가지마’, ‘나라’, ‘안녕’ 모두 해당.

 

▣ 그룹 형태(group)

패턴	기능	사용 예시	설명
[A-z] 또는 [:alpha:] 또는 \a	알파벳 대문자 또는 소문자인 문자열을 찾음	"[A-z]+"	대상 문자열에서 알파벳이 한 개 이상인 문자열을 찾음
[0-9] 또는 [:digit:] 또는 \d	숫자인 문자열을 찾음	"^[0-9]+"	한 개 이상의 숫자로 시작하는 문자열을 찾음
[^문자]	괄호 안의 문자를 포함하지 않은 문자열을 찾음	"[^부정]"	‘부’ 또는 ‘정’을 포함하지 않는 문자열을 찾음. ‘부자’, ‘정말’ 모두 제외됨

---

정규표현식 활용 in MySQL (LIKE와 REGEXP 비교)

Sample Data : Products (링크 바로가기)

# REGEXP 활용

select productid, productname, unit from products
where unit regexp 'boxes|pieces';

 

# LIKE 활용

select productid, productname, unit from products
where unit like '%boxes%' or unit like '%pieces%';

→ 동일한 결과

다른 프로그래밍 언어에서는 like와 in을 통해 여러 가지 종류의 패턴을 커버할 수 있으나,

SQL에서는 LIKE와 IN을 함께 사용할 수 없어서 위처럼 LIKE를 계속해서 병렬 연결해야 합니다.

 

그러나, 정규표현식 REGEXP를 사용한다면 깔끔하게 쿼리를 작성할 수 있기에 유용합니다.

 

# 또다른 예시1 (특수문자가 포함된 경우)

select productid, productname, unit from products
where unit regexp '[-./\,`~*+!@#$%^&()]';

→ 결과

productid	productname	unit
2	Chang	24 - 12 oz bottles
3	Aniseed Syrup	12 - 550 ml bottles
4	Chef Anton's Cajun Seasoning	48 - 6 oz jars
6	Grandma's Boysenberry Spread	12 - 8 oz jars
7	Uncle Bob's Organic Dried Pears	12 - 1 lb pkgs.
8	Northwoods Cranberry Sauce	12 - 12 oz jars
9	Mishi Kobe Niku	18 - 500 g pkgs.
10	Ikura	12 - 200 ml jars
˙˙˙˙	˙˙˙˙	˙˙˙˙

 

# 또다른 예시2 (특정 문자 및 길이 찾는 경우)

select * from products
where char_length(productname) = 5 and productname regexp 'ch'

→ 결과

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 - 12 oz bottles	19

---

References

• 정규표현식 (Regular Expression) 이해하기 [Yurim Koo]
• Like, in 같이 쓰기 해결책 REGEXP [yamea-guide]