LIKE는 텍스트를 패턴과 비교합니다. 패턴에서 %는 몇 글자든 맞고 _는 정확히 한 글자에 맞으므로, email LIKE '%@gmail.com'은 Gmail 주소를 찾습니다. LIKE는 대소문자를 구분합니다. ILIKE는 대소문자를 무시하는 같은 비교이고, ~와 ~*는 POSIX 정규식으로 비교합니다.
Asha와 Bella가 나옵니다. ILIKE는 Bella 주소의 대문자를 무시하고, Chen의 .co는 끝부분과 맞지 않습니다.
%와 _로 비교하기
문자열 전체가 패턴과 맞아야 하므로, %가 없는 패턴은 =처럼 동작합니다. NOT LIKE와 NOT ILIKE는 결과를 뒤집습니다.
_ 하나가 정확히 한 글자이므로 A-___는 A-100과는 맞지만 A-2000과는 맞지 않습니다. 소문자인 a-300은 ILIKE 검사만 통과합니다.
%나 _ 자체를 검색하기
패턴에서 백슬래시는 다음 글자를 이스케이프하므로, '%50\%%'는 50%가 들어 있는 텍스트를 찾습니다. 데이터에 백슬래시가 들어 있다면 ESCAPE로 다른 이스케이프 문자를 고르세요.
SAVE\_%는 SAVE_10과만 맞습니다. 이스케이프가 없으면 _가 SAVE10의 1과도 맞습니다.
~로 정규식 쓰기
정규식 연산자는 ~(맞음), ~*(대소문자 무시하고 맞음), !~와 !~*(맞지 않음)입니다. 정규식은 ^와 $로 고정하지 않으면 문자열 어디에서든 맞습니다.
하이픈 형식에 맞는 것은 555-0101뿐입니다. g 플래그를 쓴 regexp_replace는 숫자가 아닌 글자를 모두 지우므로, (555) 0199는 5550199가 되고 call me는 빈 문자열이 됩니다.
일치한 부분 추출하고 세기
다른 정규식 함수는 참이나 거짓 대신 일치한 내용을 돌려줍니다.
| 함수 | 반환값 |
|---|---|
substring(s from 'pattern') | 첫 번째 일치, 또는 괄호로 묶은 첫 번째 그룹 |
regexp_match(s, 'pattern') | 첫 번째 일치의 그룹들을 text[]로 |
regexp_matches(s, 'pattern', 'g') | 일치마다 한 행 |
regexp_count(s, 'pattern') | 일치 횟수 (PostgreSQL 15부터) |
regexp_substr(s, 'pattern') | 첫 번째 일치 (PostgreSQL 15부터) |
regexp_like(s, 'pattern') | ~와 같은 함수 (PostgreSQL 15부터) |
regexp_split_to_table(s, 'pattern') | 일치 사이의 조각마다 한 행 |
substring('Order 4521 shipped' from '\d+')는 4521을 돌려줍니다. 플래그와 정규식 문법은 매뉴얼의 패턴 매칭 장에 있습니다.
패턴 검색에 인덱스 쓰기
LIKE 'abc%'처럼 앞부분이 고정된 패턴은, 데이터베이스가 C 콜레이션을 쓰거나 인덱스를 text_pattern_ops로 만들었을 때 B-tree 인덱스를 쓸 수 있습니다.
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
%로 시작하는 패턴, ILIKE, 대부분의 정규식은 B-tree를 쓸 수 없습니다. 이런 경우에는 pg_trgm 확장이 LIKE, ILIKE, ~, ~*를 처리하는 트라이그램 인덱스를 제공합니다.
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
패턴이 아니라 대소문자를 무시한 일치 비교라면, lower(email)에 인덱스를 만들고 lower(email) = 'ben@work.io'로 조회하는 편이 더 간단합니다. lower, position, split_part는 문자열 함수 페이지에서 다룹니다.
자주 묻는 질문
PostgreSQL에서 문자열에 특정 문자열이 포함되어 있는지 어떻게 확인하나요?
col LIKE '%text%'를 쓰고, 대소문자를 무시하려면 col ILIKE '%text%'를 쓰세요. position('text' in col) > 0과 strpos(col, 'text') > 0도 동작하며, %나 _를 이스케이프할 필요가 없습니다.
PostgreSQL의 LIKE는 대소문자를 구분하나요?
네. 'Apple' LIKE 'a%'는 거짓입니다. ILIKE를 쓰거나, lower(col) LIKE 'a%'로 비교하거나, 대소문자를 무시하는 정규식 연산자 ~*를 쓰세요.
여러 패턴을 한 번에 비교하려면 어떻게 하나요?
LIKE ANY와 배열을 함께 씁니다. name LIKE ANY (ARRAY['a%', 'b%'])는 패턴 중 하나라도 맞으면 참이고, LIKE ALL은 모두 맞아야 합니다. name ~ '^(a|b)' 같은 정규식 선택도 같은 일을 합니다.
PostgreSQL의 SIMILAR TO는 무엇인가요?
LIKE와 정규식을 섞은 SQL 표준 문법입니다. %와 _에 더해 |, *, +, 그룹을 쓰며, 문자열 전체가 맞아야 합니다. 보통은 LIKE나 ~ 연산자가 더 알기 쉽습니다.