[SQL] MySQL 정규 표현식 사용법 (REGEXP, REGEXP_LIKE 차이점)

SELECT p_name
FROM products
WHERE REGEXP_LIKE(p_name, '^[0-9]')

정규표현식(Regular Expression)을 활용하면 다양한 텍스트 패턴을 매칭하는데 아주 효과적입니다. MySQL에서도 정규표현식을 활용할 수 있도록 함수를 제공하고 있는데요. 그중 대표적인 것이 바로 REGEXP, 그리고 REGEXP_LIKE 함수입니다. 이번 포스팅에서는 두 함수의 사용법, 차이점에 대해서 알아보겠습니다.

 

REGEXP vs REGEXP_LIKE 사용 예시

1. REGEXP: MySQL 버전 8이 나오기 전에는 내가 최고

REGEXP는 MySQL 8 버전이 나오기 전에 주로 사용되던 함수입니다. 특정 문자 패턴이 존재하는지 여부를 확인할 수 있는 함수입니다. 기본 문법은 아래와 같습니다. MySQL 공식 문서에서도 확인할 수 있습니다.

# expr REGEXP pattern
# expr RLIKE pattern

 

RLIKE는 REGEXP와 동의어로 같은 방식으로 사용이 가능합니다. 여기서 expr은 컬럼명이나 컬럼 표현식이며 pattern은 정규표현식을 의미합니다. 예를 들어 아래와 같이 사용할 수 있습니다.

SELECT p_name
FROM products
WHERE p_name REGEXP '^[0-9]';

 

정규표현식에 따라 상품명(p_name)이 숫자로 시작하는 데이터들만 불러오게 됩니다.

 

2. REGEXP_LIKE: 더 많은 기능과 옵션을 갖춰서 탄생한 강력한 함수

REGEXP_LIKE 함수는 MySQL 8.0.4 버전이 출시되면서 추가된 함수입니다. Oracle에서 사용하는 정규표현식 함수와 이름도 동일하며, 더 많은 기능과 옵션을 제공하게 되었습니다. 정규표현식과 결합해 특정 문자 패턴을 찾아낸다는 본질적인 기능 측면에서는 REGEXP, RLIKE와 동일합니다.

REGEXP_LIKE(expr, pattern[, match_type])

 

기본 문법은 위와 같습니다. 동일하게 expr은 컬럼명이나 컬럼표현식을 의미하고, pattern은 정규식, 그리고 match_type은 대소문자 구분과 같은 패턴 매칭 조건을 설정하는 파라미터입니다. 대괄호([])로 묶여있어 선택적(optional)인 파라미터임을 알 수 있습니다.

SELECT p_name
FROM products
WHERE REGEXP_LIKE(p_name, '^[0-9]')

 

REGEXP 함수의 예시와 동일하게 작성해 본 쿼리입니다. 출력 결과는 동일합니다. 이번에는 패턴매칭 조건을 설정하는 예시를 한번 알아보겠습니다. 

SELECT email
FROM users
WHERE REGEXP_LIKE(email, '@naver\.com$', 'i');

 

위 쿼리에서 REGEXP_LIKE 함수의 3번째 인자로 'i'를 전달했는데요. 이건 대소문자를 구분하지 않고(case-insensitive) 패턴을 매칭하라는 의미입니다. 아래와 같은 결과가 출력될 수 있겠네요. NAVER, naver가 모두 출력된 것을 확인할 수 있습니다.

email
--------------------
john.doe@naver.com
JANE.DOE@NAVER.COM
bob_smith@naver.com

 

REGEXP_LIKE 함수의 match_type은 아래와 같은 옵션이 있습니다. 각 옵션을 합쳐서 'in', 'cm'과 같이 사용할 수 있습니다.

- c: 대소문자 구별 (case-sensitive). 디폴트 옵션

- i: 대소문자 구별 안 함 (case-insensitive)

- m: 문자열 내에서 line terminators를 인식함. 즉 문자열을 여러 줄로 취급함

- n: 점(.) 이 줄 바꿈 문자를 포함하지 않도록 설정함

- u: 정규표현식이 유니코드 속성을 사용할 수 있게 설정함

 

거의 대소문자 구분을 위해 c, i 정도만 사용할 것으로 보입니다.

 

 

REGEXP와 REGEXP_LIKE 함수의 차이점을 정리해 보자 

	REGEXP 함수	REGEXP_LIKE 함수
사용 가능 버전	MySQL의 최신 버전을 포함해 구 버전에서도 사용 가능	MySQL 8.0.4 이후 버전에서 사용 가능
기능 측면	패턴 매칭 기능이 제한적	다양한 패턴 매칭 기능 제공
대소문자 구분	BINARY 연산자, COLLATE 절 등을 사용	함수의 세번째 매개변수로 설정할 수 있음