[SQL기본] 문자 패턴 찾기: REGEXP()

REGEXP: 정규표현식을 사용하여 패턴 매칭을 수행하는 MySQL의 연산자

이를 사용한 예시를 leetcode 문제에서 보여주겠다.

 

1. 예제 1 : 올바른 이메일 형식 찾기

Table: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
user_id is the primary key (column with unique values) for this table.
This table contains information of the users signed up in a website. Some e-mails are invalid.

 

문제

Write a solution to find the users who have valid emails.

A valid e-mail has a prefix name and a domain where:

• The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.
• The domain is '@leetcode.com'.

요약하자면 올바른 메일의 조건은 아래와 같음

 

1. 반드시 문자로 시작
2. 숫자 허용
3. underscore(_), period(.), dash(-) 허용
4. 메일은 반드시 '@leetcode.com'이어야 함.

 

이러한 조건들을 다 담을 수 있는 쿼리문이 있을까? 있다. 정규표현식을 쓰면 되는 것!

SELECT *
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\\.com$'

 

• ^[a-z]: ^ 표시는 ~로 시작한다는 뜻이다. 그리고 [a-zA-Z]는 모든 영문자를 의미한다. 따라서 첫번째 글자는 반드시 영문자로 시작한다는 말이다. ▶ 조건 1 해결!
• [a-zA-Z0-9_.-]* 
  • [a-zA-Z0-9_.-] : 모든 영문자와 숫자를 허용하며 underscore, period, dash도 허용한다는 말이다 ▶ 조건 2 ,3 해결!
  • * : [a-zA-Z0-9_.-] 가 여러번 반복해도 된다는 말이다.

 

•  @leetcode\\.com$
  • 사이에 \\가 온 이유: .(period)는 어떠한 문자와 일치하는 정규표현식 패턴이다. 따라서 문자 자체의 period(.)로 인식 되려면 반드시 슬래시가 두개 붙어야 한다. ▶ 조건 4 해결!
  • $ : 마지막을 의미한다.

 

2. 예제2 : vowel로 끝나지 않는 CITY 찾기

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contatin duplicates. The STATION table is described as follows: 

(where LAT_N is the nothern latitude and LONG_W is the western longitude.)

출처:https://www.hackerrank.com/challenges/weather-observation-station-10/problem

 

vowel(a,e,i,o,u)로 끝나지 않는 도시 이름을 찾는 문제이다. 이때 두가지 방식으로 sql을 작성할 수 있는데, 첫번째는 regexp 함수를 이용하지 않는 것이고, 두번째는 이용하는 방식이다.

 

#1. RIGHT 함수 사용

right 함수를 사용해서 city 컬럼의 맨 오른쪽 문자 1개만 추출하여 vowel이 있는지 확인하는 방식이다.

SELECT distinct CITY
FROM STATION
WHERE RIGHT(lower(city),1) NOT IN ('a','e','i','o','u');

 

#2. REGEXP 함수 사용

REGEXP 함수를 사용하여 정규 표현식을 사용한 패턴 매칭을 실행하는 것이다.

아래의 코드에서 사용한 정규 표현식의 의미는 다음과 같다.

• ^aeiouAEIOU : vowel이 아닌 문자를 찾는 것이다.
• $ : 문자의 끝을 의미한다. 즉, [^aeiouAEIOU]$ 은 문자의 끝에 vowel이 오지 않는 문자를 찾는 것이다.

SELECT distinct CITY
FROM STATION
WHERE CITY REGEXP '[^aeiouAEIOU]$'

 

3. 예제3: Vowel로 시작하지 않거나, Vowel로 끝나지 않는 CITY 찾기

예제2에서 더 나아가 CITY의 맨 앞에 vowel이 오지 않거나, 맨 뒤에 vowel이 오지 않는 도시명을 찾는다면 어떻게 쿼리를 구성해야 할까? 우선 조건을 두개 달아야 할 것이다. 1) 맨 뒤에 vowel이 오지 않는 CITY 찾기, 2) 맨 앞에 vowel 오지 않는 CITY찾기.

 

1)번 조건인 맨 뒤에 vowel이 오지 않는 조건은 예제2의 #2를 참고하면 된다. 그런데 맨 앞에 vowel이 오지 않는 경우는 어떻게 작성해야 할까. 문자열의 맨 끝을 의미하는 표현이 $ 였다면, 맨 앞을 의미하는 표현은 ^이다. 주의해야할 점은 [] 안에 ^가 있을 때는 부정의 의미이지만, [] 밖에 ^가 있을 땐 문자열의 시작을 의미한다.

정규 표현식	의미
[]$	문자열의 맨 끝
^[]	문자열의 시작
[^]	not

SELECT distinct CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiouAEIOU]'
OR CITY REGEXP '[^aeiouAEIOU]$'

 

 

4. 예제4: Vowel로 시작하지 않고, Vowel로 끝나지 않는 CITY 찾기

예제 3은 or의 문제였다. 첫번째 문자가 vowel이 아니거나, 맨 마지막 문자가 vowel이 아닌 CITY 찾기. 그런데 예제4는 and의 문제다. 첫번째 문자가 vowel로 시작하지 않고, 맨 마지막 문자도 vowel이 아닌 CITY를 찾아야 한다. 쿼리 작성 방법은 두가지가 있는데, 두번째 방식을 사용할 것을 추천한다. 더 간단하니까..!

 

#1. Where REGEXP ~ AND REGEXP 사용하기

예제3에서 OR를 썼지만, 이번엔 AND를 써서 두가지 조건을 다는 방법이다. 첫번째 조건은 '맨 앞에 vowel이 오지 않을 것', 두번째 조건은 '맨 뒤에 vowel이 오지 않을 것'. 두가지 조건을 and로 연결하여 두 조건에 모두 부합하는 답을 찾을 것.

SELECT distinct CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiouAEIOU]'
and CITY REGEXP '[^aeiouAEIOU]$'

 

#2. 하나의 REGEXP에서 모두 해결하기

#1처럼 두가지 조건을 달게 되면 쿼리식이 조금 복잡해진다. 한 눈에 알아보기 어렵기도 하다. 따라서 하나의 REGEXP 안에서 한 번에 해결해보겠다. 일단 맨 앞에 aeiou가 오지 않아야 하고, 맨 뒤에도 aeiou가 오지 않아야 하니 다음과 같이 쓸 수 있지 않을까?

• REGEXP '^[^aeiouAEIOU][^aeiouAEIOU]$'

그러나 이처럼 정규 표현식을 작성한다면 글자 수가 2개인 단어만 매칭이 된다. 예를 들면 ab, cd와 같이.

따라서 akd와 같이 문자열의 맨 앞,뒤에 모두 vowel이 없지만 글자 수가 2글자가 아닌 단어는 매칭되지 않는다. 

그렇다면.. 글자 수는 상관없이 매칭시키고 싶다면? ^[^aeiouAEIOU] 와 [^aeiouAEIOU]$ 사이에 .* 를 추가하면 된다.

정규 표현식	의미
. (period)	아무 문자 하나 (줄바꿈 제외)
*	*의 앞에 있는 문자가 0번 이상 반복됨

SELECT distinct CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiouAEIOU].*[^aeiouAEIOU]$'