LIKE сравнивает текст с шаблоном, в котором % означает любую последовательность символов, а _ ровно один символ. Так email LIKE '%@gmail.com' находит адреса Gmail. LIKE учитывает регистр. ILIKE выполняет то же сравнение без учёта регистра, а ~ и ~* проверяют регулярные выражения POSIX.
Запрос вернёт Asha и Bella: ILIKE не обращает внимания на заглавные буквы в адресе Bella, а .co у Chen не совпадает с окончанием.
Шаблоны с % и _
Шаблон должен совпасть со всей строкой, поэтому шаблон без % работает как =. NOT LIKE и NOT ILIKE инвертируют результат.
A-___ совпадает с A-100, но не с A-2000, ведь каждый _ заменяет ровно один символ. Значение в нижнем регистре a-300 проходит только проверку ILIKE.
Поиск самих символов % и _
Обратная косая черта экранирует следующий символ в шаблоне, поэтому '%50\%%' находит текст, содержащий 50%. Если обратные косые черты встречаются в самих данных, выберите другой символ экранирования через ESCAPE:
SAVE\_% совпадает только с SAVE_10. Без экранирования _ совпал бы и с 1 в SAVE10.
Регулярные выражения с ~
Операторы регулярных выражений: ~ (совпадает), ~* (совпадает без учёта регистра), !~ и !~* (не совпадает). Регулярное выражение ищет совпадение в любом месте строки, если не привязать его к началу и концу через ^ и $:
Формату с дефисом соответствует только 555-0101. regexp_replace с флагом g удаляет все нецифровые символы, так что (555) 0199 превращается в 5550199, а call me в пустую строку.
Извлечение и подсчёт совпадений
Другие функции для регулярных выражений возвращают само совпадение, а не true или false:
| Функция | Возвращает |
|---|---|
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. В главе о поиске по шаблону документации перечислены флаги и синтаксис регулярных выражений.
Как заставить поиск по шаблону использовать индекс
B-tree индекс подходит для шаблона, привязанного к началу строки, например LIKE 'abc%', если база использует сортировку C или индекс построен с text_pattern_ops:
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: им не нужно экранировать % и _.
LIKE в PostgreSQL учитывает регистр?
Да. 'Apple' LIKE 'a%' ложно. Возьмите ILIKE, сравнивайте lower(col) LIKE 'a%' или используйте оператор регулярных выражений без учёта регистра ~*.
Как проверить сразу несколько шаблонов?
Объедините LIKE ANY с массивом: name LIKE ANY (ARRAY['a%', 'b%']) истинно, если совпал хотя бы один шаблон, а LIKE ALL требует совпадения всех. То же самое делает альтернатива в регулярном выражении, например name ~ '^(a|b)'.
Что такое SIMILAR TO в PostgreSQL?
Это смесь LIKE и регулярных выражений из стандарта SQL: в нём есть % и _, а также |, *, + и группы, и шаблон должен совпасть со всей строкой. Обычно LIKE или операторы ~ читаются понятнее.