Menu

PostgreSQL LIKE, ILIKE и регулярные выражения

LIKE сравнивает текст с шаблоном, где есть % и _, ILIKE делает то же без учёта регистра, а операторы ~ проверяют регулярные выражения POSIX.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

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 или операторы ~ читаются понятнее.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать