SQL에서 패턴을 찾아주는 LIKE 활용하기

게시일 : 2018년 03월 26일     수정일 : 2019년 01월 09일    
# MySQL # LIKE # LIKE BINARY # PostgreSQL # ILIKE

CodeSignal SQL 예제를 바탕으로 LIKE 활용법에 대해 알아본다.

users 테이블

id first_name second_name attribute
1 Mikel Cober 3%Mikel_Cober%dsfew
2 Tosha Chace TESTdDfwerwYz%Tosha_Chace%werhfsWhixc
3 Carrol Bean 1TESTzhncdDfwerwYz%carrol_bean%weornsWIJAxc
4 Mack Alice asyvwTESTncdDfUWYz%MACk_ALice%wehrJUWBaXAxc
5 Vicenta Kravitz Tn35g5h51u7lt1231W946J
6 Kevin Heart kUJ3N67vLB07mQL9Ai7p18cXGzjdT32r8283ZQi

와일드카드 _ & %

_는 한 글자만을 의미하며, %는 0글자부터 그 이상을 의미한다.

%와 % 사이에 텍스트를 넣으면 해당 텍스트가 포함된 모든 raw를 불러올 수 있다.

# TEST 중간에 들어가는 모든 raw 불러온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';
id first_name second_name attribute
2 Tosha Chace TESTdDfwerwYz%Tosha_Chace%werhfsWhixc
3 Carrol Bean 1TESTzhncdDfwerwYz%carrol_bean%weornsWIJAxc
4 Mack Alice asyvwTESTncdDfUWYz%MACk_ALice%wehrJUWBaXAxc
SELECT * FROM users WHERE attribute LIKE '_TEST%';
=> TEST 앞에 1만 존재하는 3번 raw만 가져온다.

와일드카드 없이 텍스트로 시작하거나 끝나면 해당 텍스트로 시작되거나 끝나야 한다.

SELECT * FROM users WHERE attribute LIKE 'TEST%';
=> TEST 시작하는 2 raw 가져온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%Axc';
=> Axc 끝나는 3,4 raw 가져온다.

case sensitivity

case sensitivity는 대소문자를 구별하느냐의 차이를 의미한다.

MySQL - case-insensitive <-> BINARY

MySQL에서 LIKE는 기본적으로 case-insensitive하다. 즉, 대소문자를 구별하지 않는다.

# 모두 같은 결과를 가져온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';
SELECT * FROM users WHERE attribute LIKE '%test%';
SELECT * FROM users WHERE attribute LIKE '%TeSt%';

case-sensitive 하려면 즉, 대소문자를 구별하여 패턴을 찾기 위해서는 BINARY를 사용해야 한다.

SELECT * FROM users WHERE attribute LIKE BINARY '%TEST%';

PostgreSQL - case-sensitive <-> ILIKE

PostgreSQL에서는 MySQL과는 반대로 case-sensitive하다. 즉, 대소문자를 구별한다.

case-insensitive 하려면 ILIKE를 사용해야 한다. ILIKE는 PostrgreSQL에서만 지원하는 방식이다.

SELECT * FROM users WHERE attribute LIKE '%TEST%'; # case-sensitive
SELECT * FROM users WHERE attribute ILIKE '%tEsT%'; # case-insensitive

이번 문제는 attribute에서 %{first_name}_{second_name}%의 구조를 가진 raw를 찾는 것이다. 단, 이름 철자 그대로 적용되어야 한다.

예를 들면, 1번 raw에서 Mike Cober은 %Mikel_Cober%형태를 띄고 있다. 하지만, 4번 raw의 경우, %MACk_ALice%형태로 대소문자에 문제가 있음을 볼 수 있다.

CONCAT은 column value와 텍스트를 이어주는 역할을 한다. 와일드카드를 문자로 활용하기 위해서 \를 앞에 붙여야 한다.

# MySQL 기준
# 와일드카드 % % 사이에 %{fisrt_name}_{second_name}% 구조를 넣은 형식이다.
SELECT * FROM users WHERE attribute 
    LIKE BINARY CONCAT('%\%', first_name , '\_' , second_name ,'\%%')

3번은 모두 소문자이고, 4번은 대소문자가 다르므로 정확하게 입력된 건 1,2번 raw뿐이다.

id first_name second_name attribute
1 Mikel Cober 3%Mikel_Cober%dsfew
2 Tosha Chace TESTdDfwerwYz%Tosha_Chace%werhfsWhixc