Операторы ~ и ~*: где LIKE сдаётся

Глава 1 — «Основы: поиск по шаблону вместо LIKE»

Ты входишь в Сортировочную «Хранилища-9». За дверью гудят стойки, а на янтарной панели вперемешку дрожат обрывки первого сигнала: жалоба на доставку, телефон, адрес, строка журнала сервера. Дешифратор разобрал таблицы «Котомаркета», а этот хвост сигнала так и бросил здесь.

Комендант выводит на экран первое обращение: Здравствуйте! Заказ KM-2024-001234 до сих пор не пришёл. Открывает восьмое — там код спрятан внутри ссылки. В четырнадцатом письме таких кодов сразу несколько. А в третьем тот же префикс набран маленькими буквами: km-2024-000777.

Перебирать известные коды по одному бессмысленно: сегодня их сотня, завтра тысяча. Нужна запись, которая описывает общий вид кода и ищет этот вид внутри текста.

КВЕРИ: У обрывков разные номера, но почерк один. Его и будем ловить.

Такую запись называют регулярным выражением, или просто регуляркой. До специальных значков ещё дойдём. Сначала возьмём обычные буквы и посмотрим, чем этот поиск отличается от знакомого LIKE.

Найти по описанию

Возьми шаблон кот. Никакой магии в нём пока нет: три обычные буквы. Читаем слева направо — сначала «к», сразу за ней «о», потом «т».

Приложим его к строке кот. Первая буква подошла, вторая тоже, третья тоже. Получился непрерывный кусок из трёх символов. Такой найденный кусок текста называется совпадением.

Со строкой кит всё ломается на второй букве. Шаблон ждёт «о», а получает «и», поэтому в этом месте совпадения нет. А вот в строке мой кот спит начало нам вообще не мешает: поиск доберётся до кот в середине строки и найдёт нужные три буквы.

Регулярное выражение описывает, какой фрагмент текста должен подойти. Эту запись ещё называют шаблоном. В документации встретишь английские названия regular expression и regex.

Пока шаблон выглядит почти слишком простым. Это даже хорошо. Когда дальше появятся специальные знаки, привычка читать регулярку по кусочкам сильно сэкономит нервы. Чужой шаблон на полэкрана я тоже не пытаюсь понять одним взглядом: иду слева направо и выясняю, что требует каждый участок.

Теперь KM-. Здесь снова три точных символа подряд: латинская K, латинская M, дефис. В строке Заказ KM-2024-001234 такой кусок есть, поэтому совпадение найдётся. В Промокод KOTO-2024 не найдётся: после K стоит O, хотя шаблон ждёт M.

Регулярка ничего не знает о заказах и промокодах. Для неё перед глазами только символы. Написали KM- — она ищет K, потом M, потом -. Это полезно помнить каждый раз, когда хочется приписать шаблону чуть больше здравого смысла, чем в нём записано.

Где такой поиск встречается

Рабочие данные редко выглядят как аккуратный учебный пример. В контактах один человек пишет Москва, другой — г. Москва, третий — москва. Код заказа в обращении может стоять после приветствия, прятаться внутри ссылки или встречаться несколько раз. В серверном логе адрес страницы окружён временем запроса, кодом ответа и названием браузера.

Поэтому часто ищут не целую строку, а фрагмент определённого вида. Редакторы кода умеют искать по регулярке. grep делает то же самое в текстовых файлах. В формах шаблоном можно проверить, похож ли введённый текст на ожидаемый формат. При чистке выгрузки регулярка быстро выдаёт лишние пробелы или разные варианты одного разделителя.

У такого поиска есть чёткая граница. Регулярка проверяет форму текста. Строка может выглядеть как адрес почты, но из этого не следует, что ящик существует. Набор цифр может быть похож на телефон, но регулярка не узнает, принадлежит ли этот номер кому-нибудь.

В нашем архиве три таблицы: contacts с сырыми контактами, tickets с обращениями покупателей и access_log с журналом сервера. Начнём с tickets. В обычной фразе проще глазами увидеть, где лежит совпадение и почему запрос его взял.

Откуда взялись регулярные выражения

Сам термин появился задолго до PostgreSQL и привычных редакторов. В 1951 году математик Стивен Клини описывает теорию, связанную с регулярными множествами и регулярными языками. «Язык» здесь понимается математически: это набор последовательностей, которые подчиняются определённым правилам. Работа выходит как отчёт RAND, а в 1956 году появляется известная публикация в книге.

К концу 1960-х Кен Томпсон переносит эти идеи в поиск по тексту: сначала в редактор QED, затем в ed. Эту историю подробно описывает Деннис Ритчи. Из команды ed, которая искала строки и печатала их, позже вырос grep.

История у регулярных выражений серьёзная, а наша первая задача вполне приземлённая: найти KM- внутри письма. Для неё пока хватит трёх обычных символов.

Сначала знакомый поиск: LIKE

В первом обращении код окружён другим текстом. Слева стоит Здравствуйте! Заказ , справа продолжается фраза. Условие body LIKE 'KM-' такую строку не пропустит: шаблон LIKE сопоставляется со всей строкой целиком.

Поэтому кусок внутри строки через LIKE обычно ищут так: %KM-%.

Разберём, что здесь происходит. Первый % берёт сколько угодно любых символов до нужного места. Затем обязаны подряд идти K, M и -. Второй % берёт остаток строки. Для Заказ KM-2024-001234 готов первый процент соответствует Заказ , затем совпадает KM-, а последний процент покрывает 2024-001234 готов.

Есть хороший крайний пример: строка состоит только из KM-. Она тоже подходит. Оба % могут соответствовать пустому фрагменту, поэтому символы до и после KM- не обязательны.

У LIKE есть ещё _: он соответствует ровно одному любому символу. Но «любой символ» быстро становится проблемой, если по условию нужна именно цифра. На месте _ с тем же успехом пройдёт буква. Более точные правила для кодов появятся позже, а сейчас нам нужна разница в самой механике поиска.

До запуска ячейки сделай два прогноза. Попадёт ли обращение 1, хотя KM- находится в середине длинной фразы? И что будет с обращением 3, где написано строчное km-?

Тильда: есть ли совпадение?

Возьмём тот же текст, но вместо LIKE поставим оператор PostgreSQL ~. Условие body ~ 'KM-' спрашивает: встречается ли где-нибудь внутри body кусок, подходящий под KM-?

Для каждой строки ответ всего один: true или false. Если такое условие стоит в WHERE, PostgreSQL оставит строки, для которых получилось true.

Шаблон пока читается буквально: K, потом M, потом -. Символы, которые обозначают сами себя, называют литеральными. Даже эти три литеральных символа уже составляют регулярное выражение, хотя специальных знаков внутри пока нет.

Вот первая разница, которую стоит закрепить. LIKE сопоставляет свой шаблон со всей строкой, поэтому для поиска внутри мы добавляли % по краям. ~ сам ищет подходящее место внутри текста. Писать вокруг KM- проценты не требуется.

Можно представить короткую прозрачную рамку на три клетки с KM-. Поиск ставит её в начало строки. Не подошло — сдвигает дальше. Снова не подошло — ещё дальше. В обращении 1 рамка доезжает до места после слова Заказ и там наконец совпадает всеми тремя клетками.

На схеме рядом эти два подхода стоят на одной строке: регулярка находит место для KM-, а LIKE с % описывает всё значение от начала до конца.

Здесь я часто вижу одну и ту же ошибку: после нескольких лет с LIKE пальцы сами набирают %KM-% и внутри регулярки. Не переноси этот синтаксис по привычке. У % в регулярном выражении нет той роли, которую он играет в LIKE.

И ещё не додумывай за шаблон. Проверка body ~ 'KM-' подтверждает только наличие символов KM-. Строка сломано: KM- тоже даст true, хотя номера после дефиса нет вообще. Мы попросили PostgreSQL найти три символа, и ровно это он сделал.

Проверим обе механики на четырёх коротких строках. Здесь уже не спрячешься за длинным текстом письма: видно каждый символ.

Заказ KM-2024-001234 готов подходит и шаблону %KM-%, и регулярке KM-. В случае LIKE проценты забирают начало и конец значения. Регулярка просто находит KM- посередине.

Строка KM- тоже подходит обоим условиям. У LIKE оба % соответствуют пустым кускам. Для регулярки найденный фрагмент просто занимает всю строку.

km-2024-000777 не подходит ни одному из этих двух условий. В обоих шаблонах записаны заглавные K и M, а в строке стоят строчные k и m.

Осталась строка %KM-%. Сначала попробуй ответить без запуска. Для LIKE '%KM-%' настоящие проценты внутри самого значения могут попасть под внешние %, а KM- посередине совпадёт буквально. Регулярке KM- тоже всё равно, что вокруг стоят символы %: свой фрагмент она там найдёт.

Большие и маленькие буквы

Комендант просматривает выдачу и замечает дыру: обращение 3 пропало. В нём есть km-2024-000777, но запрос с ~ 'KM-' его не выбрал.

Причина видна на первой же букве. Шаблон ждёт заглавную K, а в тексте стоит строчная k. Проверка этого места сразу проваливается. Оператор ~ учитывает регистр, поэтому для него K и k различаются.

На русском это работает так же. Шаблон кот найдёт строчное кот в обращении 3. Позже в той же строке встретится Кот с большой первой буквой, и туда этот шаблон уже не подойдёт.

Если регистр по задаче неважен, в PostgreSQL есть оператор ~*. Обрати внимание на запись: звёздочка относится к SQL-оператору и стоит за пределами кавычек. Сам шаблон внутри можно оставить обычным: km-.

Условие body ~* 'km-' подходит к km-, KM-, Km- и kM-. Разница в регистре букв исчезает. Дефис при этом остаётся обязательным и должен идти после двух букв.

До запуска снова сделай прогноз. Обращение 3 теперь должно появиться. А обращения 1 и 14 с заглавным KM- никуда не денутся: ~* принимает оба регистра, а не требует, чтобы буквы обязательно были строчными.

Перед следующей ячейкой одну строку разберём целиком, а на второй подсказку уберём.

Для кот всё просто: ~ 'кот' находит все три буквы, значит found = true. ~* тоже даёт true. Отрицательные операторы отвечают наоборот, поэтому обе последние колонки будут false.

Теперь возьми Кот и мысленно заполни четыре значения слева направо. Удобный порядок такой: сначала ответь, что получится у ~ и ~*, затем для !~ и !~* просто переверни соответствующий результат.

Отдельно присмотрись к скотч. Человек видит название канцелярской ленты. Движок идёт по символам с-к-о-т-ч, а посередине действительно стоят подряд к, о, т.

Маркер: покажи найденный кусок

true отвечает на вопрос запроса, но при отладке этого часто мало. Возьми обращение 12: Почему у кота на главной написано «кот кот»? Опечатка? Условие body ~ 'кот' подтвердит совпадение, но не покажет, какой кусок строки сработал и где он находится.

Поэтому у КВЕРИ есть маркер. Готовая запись regexp_replace(body, 'кот', '⟦\&⟧', 'g') оставляет строку на месте и подсвечивает каждый найденный фрагмент. Скобки ⟦\&⟧ в запросе — заготовка маркера: в выводе ячейки они превращаются в цветной фон, и сами скобки ты не увидишь.

Сейчас не разбирай устройство regexp_replace. До него курс доберётся в четвёртой главе. Для проверки достаточно менять шаблон во втором аргументе и смотреть, что подсветилось.

Например, есть кусок у кота. Шаблон содержит ровно к, о, т, поэтому результат будет у кота. Буква а осталась снаружи: её никто не просил искать.

На скотч маркер покажет скотч. Такой вывод быстро отрезвляет. В голове задача могла звучать как «ищем слово кот», но запрос на самом деле требует только три соседние буквы.

С регистром тоже всё видно сразу. В обращении 3 встречаются оба варианта: строчное кот и заглавное Кот. Маркер в следующей ячейке использует шаблон кот с учётом регистра, поэтому первое совпадение подсветится, а второе останется как было.

Я обычно проверяю новую регулярку не только числом найденных строк. Две-три строки с таким маркером часто сразу показывают, что шаблон ухватил лишний символ или залез внутрь слова.

Теперь без подробного разбора. Есть четыре строки: кот, кота, скотч, Кот. До запуска реши, какие буквы подсветятся в каждой из них.

В первой совпадение займёт всю строку. Во второй один символ останется справа. В третьей по одному символу останется с обеих сторон. Последняя строка проверит, не забыл ли ты про регистр.

Если прогноз где-то не совпадёт с результатом, снова прочитай шаблон буквально. В кот записаны только три символа. Ни понятия «слово», ни границ слова, ни догадок о смысле в этом шаблоне пока нет.

Совпадение не обязано быть словом

скотч уже показал ловушку, на которой спотыкаются почти все в начале. Шаблон кот требует только три соседние буквы: к, о, т. Что стоит перед ними и после них, этому шаблону безразлично.

Поэтому в кота получаем кота. В скотч — скотч. В котлета совпадение снова начинается с первого символа: котлета.

КВЕРИ: Кота в скотче я не вижу. Три нужные буквы подряд — вижу. Поиск со мной согласен.

В боевых данных такая ошибка уже не выглядит шуткой. В задаче написано «найди слово», человек переносит в шаблон только буквы слова и автоматически представляет границы по краям. PostgreSQL этих воображаемых границ не видит.

Как потребовать именно отдельное слово, разберём в третьей главе. Пока пользуйся более простым способом проверки: проговори шаблон буквально и посмотри маркером на несколько реальных совпадений.

Комендант переключает экран на contacts. Теперь нужны записи из Москвы. В city лежат Москва, г. Москва и москва. Здесь сразу пригодятся обе вещи, которые мы уже проверили: регулярка умеет находить кусок внутри более длинного значения, а чувствительность к регистру задаётся оператором.

Последняя задача уже без разбора по шагам. В сломанном запросе стоит city = 'Москва'. Оператор = требует точного совпадения всего значения, поэтому г. Москва отсекается из-за приставки, а москва ещё и отличается регистром.

Коменданту нужны все московские записи из этой выгрузки: и г. Москва, и москва. Нужный оператор ты уже использовал. Он ищет фрагмент внутри строки и не различает большие и маленькие буквы.

Частые ошибки

Перенести % из LIKE в регулярку

После LIKE '%KM-%' рука легко продолжает по инерции и печатает body ~ '%KM-%'. Но % принадлежит правилам LIKE. В регулярном выражении он не означает «любой текст по краям».

Для строки Заказ KM-2024-001234 в сегодняшней задаче достаточно body ~ 'KM-'. Если сомневаешься, вынеси короткую строку в отдельный пример и посмотри маркером, какой кусок реально совпал. Для обычного поиска KM- внутри строки проценты не нужны.

Забыть про регистр

В обращении 3 записано km-2024-000777. Глаз быстро принимает KM- и km- за один префикс, но ~ вернёт false: уже первая заглавная K не совпадает со строчной k.

Когда оба регистра допустимы, используй ~*. Для отрицательной проверки без учёта регистра есть !~*. Я бы не стал ради одного такого условия заранее переводить колонку в нижний регистр: PostgreSQL уже даёт оператор для нужной проверки.

Принять кусок слова за отдельное слово

Выражение скотч ~ 'кот' возвращает true. Первое желание возразить понятно: скотч ведь не кот. Но движок видит символы, и внутри с-к-о-т-ч три буквы шаблона действительно стоят подряд.

Маркер показывает это без споров: скотч. Если задача требует отдельное слово, одного шаблона кот мало. Границы появятся в третьей главе. До тех пор не добавляй их в голове за PostgreSQL.

Перепутать отрицание с удалением совпадения

Для строки Кот выражение !~ 'кот' даёт true. Иногда !~ поначалу читают как «убрать найденное» или «показать всё, кроме совпадения». Но оператор вообще не меняет текст. Он проверяет, отсутствует ли совпадение, и возвращает логический ответ.

На пёс это особенно заметно: пёс !~ 'кот' тоже равно true, хотя вырезать из строки нечего. Изменение текста — отдельная операция.

Посчитать строки вместо совпадений

В обращении 14 KM- встречается три раза. Запрос WHERE body ~ 'KM-' всё равно возвращает это обращение одной строкой. WHERE работает с записью таблицы целиком: либо оставляет её, либо отбрасывает.

Поэтому количество строк в выдаче ничего не говорит о числе совпадений внутри одного body. В этом уроке проверяй такие случаи маркером: он подсветит каждый найденный кусок, и три совпадения будут видны прямо в тексте.

Как проверять свой первый шаблон

Когда возьмёшь свою текстовую колонку, не начинай с регулярки на двадцать символов. Сначала достань две-три настоящие строки и словами запиши, что именно должно встретиться в тексте.

Для нашего префикса это звучит так: «заглавная K, сразу после неё заглавная M, затем дефис». Запись почти сама превращается в KM-. Потом отдельно реши вопрос с регистром. Если большие и маленькие буквы должны считаться одинаковыми, в PostgreSQL бери ~*.

После удачных примеров обязательно подложи неудобный. В архиве это обращение 3 со строчным km-. Для шаблона кот таким тестом стал скотч. Один неприятный пример часто рассказывает о регулярке больше, чем пять строк, на которых всё случайно сработало как задумано.

И в конце посмотри на совпадение маркером. Ожидал отдельный кот, а получил скотч? Значит, искать ошибку уже не надо: PostgreSQL прямо показал, какое правило ты ему написал.