Регулярные выражения в SQL: regexp_replace, проверка формата и примеры

Коротко

Регулярное выражение — шаблон, который описывает форму строки: какие символы, сколько раз и в каком порядке. В SQL им решают четыре задачи: проверить формат, достать часть строки, заменить и разбить.

• Проверка в PostgreSQL — оператор ~. В DuckDB тот же оператор сравнивает строку с шаблоном целиком. Шаблон вида ^…$, описывающий всю строку, работает в обоих движках одинаково.
• regexp_replace(строка, шаблон, замена, 'g') одинаков в PostgreSQL и DuckDB. Без флага g меняется только первое совпадение.
• Извлечение: в PostgreSQL substring(строка from шаблон), в DuckDB regexp_extract. Общий способ — regexp_replace со ссылкой на группу \1.
• Регистр отключает (?i) в начале шаблона — это понимают оба движка.
• Точка, плюс, скобки и другие служебные символы экранируются обратной косой чертой: \. — именно точка.
• Регулярка проверяет форму, а не смысл: адрес a@b.cc формально верен, но писем на него никто не получит.

Когда LIKE уже не хватает

У LIKE два знака: % — сколько угодно любых символов и _ — ровно один. Этого достаточно для «начинается с», «заканчивается на» и «содержит»; подробно — в статье про LIKE и ILIKE. Но в шаблоне LIKE нельзя сказать «только цифры», «одно из двух слов» или «от двух до четырёх букв».

Пример на учебной базе: нужны события, название которых состоит из двух слов и заканчивается на created или sent. Через LIKE это два условия с OR, и ни одно из них не проверяет, что перед подчёркиванием стоят буквы. Регулярное выражение описывает всё сразу: ^[a-z]+_(created|sent)$ — от начала строки одна или больше строчных латинских букв, подчёркивание, одно из двух слов и конец строки.

Запрос находит три события из пяти — 5660 строк из 35 341. Блок выполняется в песочнице как есть и даёт тот же результат в PostgreSQL.

select event_name, count(*) as events
from events
where event_name ~ '^[a-z]+_(created|sent)$'
group by event_name
order by events desc;

-- workspace_created | 2750
-- report_created    | 1775
-- invite_sent       | 1135

Синтаксис регулярных выражений: якоря, классы, квантификаторы, группы

Шаблон читается слева направо. Обычная буква или цифра означает саму себя, а служебные символы задают правила. Для повседневных задач аналитика хватает набора из таблицы — он одинаков в PostgreSQL и DuckDB.

Запрос под таблицей применяет пять шаблонов к названиям событий. Результат удобно читать по столбцам. Из двух слов через подчёркивание состоят все пять названий. С report или export начинаются два. Ровно восемь символов — только в app_open. На ed или en заканчиваются все, кроме invite_sent. Хвост из четырёх букв после подчёркивания есть у app_open и invite_sent.

select event_name,
       event_name ~ '^[a-z]+_[a-z]+$'    as two_words,
       event_name ~ '^(report|export)_.*$' as report_or_export,
       event_name ~ '^.{8}$'             as eight_chars,
       event_name ~ '^.*e[dn]$'          as ends_ed_or_en,
       event_name ~ '^[^_]+_[a-z]{4}$'   as tail_of_four,
       count(*)                          as events
from events
group by event_name
order by events desc;

-- app_open          | true | false | true  | true  | true  | 28693
-- workspace_created | true | false | false | true  | false | 2750
-- report_created    | true | true  | false | true  | false | 1775
-- invite_sent       | true | false | false | false | true  | 1135
-- export_completed  | true | true  | false | true  | false | 988

Как проверить формат email, телефона и промокода

Проверка формата — это шаблон, который описывает строку от первого символа до последнего. Якоря ^ и $ здесь обязательны: без них в PostgreSQL условию хватит совпадения в любой части строки, и кот 79161234567 дома пройдёт проверку телефона.

В учебной базе нет ни адресов, ни телефонов, поэтому данные формы собраны прямо в запросе — это учебная вставка через VALUES. Три шаблона: адрес из строчных букв, цифр и нескольких знаков с доменом и зоной от двух букв; телефон в виде +7 и десяти цифр; промокод из заглавных букв, дефиса и двух цифр.

Результат стоит прочитать построчно. Вторая строка не прошла ни одной проверки: в адресе пробелы по краям и заглавные буквы, телефон записан через пробелы, промокод набран строчными. У третьей в адресе нет доменной зоны. В четвёртой два адреса в одном поле, а промокод длиннее на символ. В пятой телефон не заполнен, и проверка вернула NULL, а не false: строка с пустым телефоном не попадёт ни в «годные», ни в «негодные», если не обработать NULL отдельно.

Шаблон для email здесь намеренно грубый. Он ловит пробелы, пропущенную зону и два адреса в одном поле, но не доказывает, что ящик существует.

with signup_form(id, email, phone, promo) as (
  values
    (1, 'anna@mayak.example', '+7 (916) 123-45-67', 'WELCOME-10'),
    (2, ' Ivan.Petrov@Example.COM ', '8 916 765 43 21', 'welcome-10'),
    (3, 'olga@corp', '916-555-00', 'SALE2026'),
    (4, 'support@mayak.example, sales@mayak.example', '+79160001122', 'TEAM-25X'),
    (5, 'kate_s@example.org', null, 'SPRING-05')
)
select id,
       email ~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$' as email_ok,
       phone ~ '^\+7\d{10}$'                             as phone_ok,
       promo ~ '^[A-Z]+-\d{2}$'                           as promo_ok
from signup_form
order by id;

Оператор ~ в PostgreSQL: поиск по части строки, ~* и !~

В PostgreSQL оператор ~ ищет совпадение в любом месте строки: event_name ~ 'created' истинно для report_created. У него три родственника: ~* — то же без учёта регистра, !~ — «не соответствует», !~* — «не соответствует без учёта регистра».

В DuckDB оператор ~ устроен иначе: это полное совпадение, строка должна соответствовать шаблону целиком. Тот же event_name ~ 'created' в песочнице не находит ни одной строки, хотя в PostgreSQL их 4525. Ошибки при этом нет — просто пустой результат. Оператора ~* в DuckDB нет совсем. Поиск по части строки там делает функция regexp_matches(event_name, 'created').

Отсюда рабочее правило статьи: если запрос должен выполняться и в песочнице, и в PostgreSQL, пишите шаблон на всю строку — ^.*created$ вместо created$. Блок ниже — только для PostgreSQL.

select count(*) filter (where event_name ~ 'created')            as has_created,
       count(*) filter (where event_name ~* 'CREATED$')          as any_case,
       count(*) filter (where event_name !~ '_(created|sent)$')  as other_events,
       count(*) filter (where event_name ~ '^report')            as starts_with_report
from events;

-- 4525 | 4525 | 29681 | 1775

Как достать часть строки: домен из email и UTM-метку из URL

Когда разделитель один и стоит на постоянном месте, хватает split_part — он разобран в статье про строковые функции. Регулярное выражение нужно, когда нужный кусок плавает: метка utm_source может стоять в адресе первой, последней или отсутствовать.

Общий для двух движков способ — regexp_replace со ссылкой на группу. Шаблон описывает строку целиком, нужная часть взята в скобки, а вся строка заменяется на \1, то есть на содержимое скобок. В примере [^&#]+ означает «всё до амперсанда или решётки»: значение метки кончается там, где начинается следующий параметр или якорь страницы.

У приёма есть ловушка. Если шаблон не совпал, regexp_replace ничего не заменяет и возвращает исходную строку — в столбце с меткой окажется адрес целиком. Поэтому результат обёрнут в nullif(…, landing_url): когда замены не было, получается NULL. Так в третьей строке вместо адреса блога стоит пустое значение.

В PostgreSQL для извлечения есть прямые инструменты. substring(строка from шаблон) возвращает содержимое первой группы, а без скобок в шаблоне — всё совпадение; если совпадения нет, получается NULL. regexp_match возвращает массив всех групп, нужную берут по номеру. В DuckDB обе записи не выполняются: там эту работу делает regexp_extract(строка, шаблон, 1), который при отсутствии совпадения возвращает пустую строку, а не NULL.

with signup_form(id, email, landing_url) as (
  values
    (1, 'anna@mayak.example', 'https://mayak.example/?utm_source=yandex&utm_medium=cpc'),
    (2, ' Ivan.Petrov@Example.COM ', 'https://mayak.example/pricing?utm_medium=email&utm_source=newsletter'),
    (3, 'olga@corp', 'https://mayak.example/blog'),
    (4, 'kate_s@example.org', 'https://mayak.example/?ref=1&utm_source=vk#top')
)
select id,
       lower(regexp_replace(trim(email), '^.*@', '')) as domain,
       nullif(
         regexp_replace(landing_url, '^.*utm_source=([^&#]+).*$', '\1'),
         landing_url
       ) as utm_source
from signup_form
order by id;

-- 1 | mayak.example | yandex
-- 2 | example.com   | newsletter
-- 3 | corp          | NULL
-- 4 | example.org   | vk

with signup_form(id, email, landing_url) as (
  values
    (1, 'anna@mayak.example', 'https://mayak.example/?utm_source=yandex&utm_medium=cpc'),
    (2, ' Ivan.Petrov@Example.COM ', 'https://mayak.example/pricing?utm_medium=email&utm_source=newsletter'),
    (3, 'olga@corp', 'https://mayak.example/blog'),
    (4, 'kate_s@example.org', 'https://mayak.example/?ref=1&utm_source=vk#top')
)
select id,
       lower(substring(email from '@([^@\s]+)'))             as domain,
       substring(landing_url from 'utm_source=([^&#]+)')      as utm_source,
       (regexp_match(landing_url, 'utm_(medium|source)=([^&#]+)'))[2] as first_utm_value
from signup_form
order by id;

-- 1 | mayak.example | yandex     | yandex
-- 2 | example.com   | newsletter | email
-- 3 | corp          | NULL       | NULL
-- 4 | example.org   | vk         | vk

regexp_replace: как заменить по шаблону и почистить пробелы и мусор

У regexp_replace четыре аргумента: строка, шаблон, замена и флаги. В PostgreSQL и DuckDB без флагов меняется только первое совпадение: regexp_replace('a1b22c333', '[0-9]+', '#') даёт a#b22c333. Флаг 'g' меняет все совпадения — a#b#c#. Забытый g — самая частая причина того, что чистка «сработала наполовину».

Три типовые чистки. Телефон: \D — любой символ, кроме цифры, и замена на пустую строку оставляет одни цифры. Ведущая восьмёрка меняется на семёрку вторым вызовом с якорем ^8 — здесь g не нужен, начало у строки одно. Имя: \s+ — один или больше пробельных символов, включая табуляцию; замена на один пробел схлопывает повторы, а trim убирает пробелы по краям.

После чистки проверка формата становится простой: ^7\d{10}$. Три телефона из четырёх теперь годные, а в третьей строке осталось восемь цифр — такой номер чисткой не спасти, его нужно показать как ошибку ввода. Чистите в отдельном столбце и не затирайте исходное значение: по нему потом выясняют, что именно сломалось.

with signup_form(id, full_name, phone) as (
  values
    (1, 'Анна Орлова', '+7 (916) 123-45-67'),
    (2, '  Иван   Петров ', '8 916 765 43 21'),
    (3, 'Ольга' || chr(9) || 'Ким', '916-555-00'),
    (4, 'Катя  Сомова', '+79160001122')
),
cleaned as (
  select id,
         trim(regexp_replace(full_name, '\s+', ' ', 'g')) as full_name,
         regexp_replace(regexp_replace(phone, '\D', '', 'g'), '^8', '7') as phone_digits
  from signup_form
)
select id, full_name, phone_digits,
       phone_digits ~ '^7\d{10}$' as phone_ok
from cleaned
order by id;

-- 1 | Анна Орлова | 79161234567 | true
-- 2 | Иван Петров | 79167654321 | true
-- 3 | Ольга Ким   | 91655500    | false
-- 4 | Катя Сомова | 79160001122 | true

Как разбить строку по шаблону

В одно поле иногда попадает несколько значений: адреса через запятую, через пробел или через то и другое сразу. regexp_split_to_table(строка, шаблон) режет строку по каждому совпадению шаблона и возвращает по строке результата на каждый кусок. Функция есть и в PostgreSQL, и в DuckDB.

Шаблон [,\s]+ читается так: одна или больше запятых и пробельных символов подряд. Запятая с пробелом считается одним разделителем, и отдельный trim не нужен. Из двух строк формы получается четыре строки результата — после этого адреса можно считать, проверять и соединять с другими таблицами. Если удобнее получить массив в одной ячейке, есть парная функция regexp_split_to_array.

with signup_form(id, email) as (
  values
    (1, 'anna@mayak.example'),
    (2, 'support@mayak.example, sales@mayak.example help@mayak.example')
)
select id, regexp_split_to_table(email, '[,\s]+') as email
from signup_form
order by id, email;

-- 1 | anna@mayak.example
-- 2 | help@mayak.example
-- 2 | sales@mayak.example
-- 2 | support@mayak.example

Как не учитывать регистр

Регулярное выражение различает регистр: [A-Z] — только заглавные. Способов отключить это три. Первый — (?i) в начале шаблона, его понимают оба движка. Второй — флаг 'i' в regexp_replace, его можно сочетать с g: 'gi'. Третий — привести строку к одному регистру до проверки через lower или upper. В PostgreSQL есть ещё оператор ~*, в DuckDB его нет.

В примере строгий шаблон пропускает один промокод из пяти, а с (?i) — три. SALE2026 и TEAM-25X не проходят в обоих случаях: дело в форме, а не в регистре.

С кириллицей есть две тонкости. Диапазон [а-я] не включает букву «ё», её добавляют явно: [а-яё]. А класс \w в PostgreSQL считает буквами и кириллицу, в DuckDB — только латиницу, цифры и подчёркивание: 'Ёлка' ~ '^\w+$' истинно в первом и ложно во втором. Для русских слов надёжнее перечислить диапазон явно и добавить (?i).

with promo_codes(promo) as (
  values ('WELCOME-10'), ('welcome-10'), ('Spring-05'), ('SALE2026'), ('TEAM-25X')
)
select promo,
       promo ~ '^[A-Z]+-\d{2}$'      as strict_case,
       promo ~ '(?i)^[A-Z]+-\d{2}$'  as any_case,
       regexp_replace(promo, '[a-z]+', '#', 'gi') as letters_masked
from promo_codes
order by promo;

-- SALE2026   | false | false | #2026
-- Spring-05  | false | true  | #-05
-- TEAM-25X   | false | false | #-25#
-- WELCOME-10 | true  | true  | #-10
-- welcome-10 | false | true  | #-10

Жадные и ленивые квантификаторы: почему захватилось слишком много

Квантификаторы * и + по умолчанию жадные: забирают столько символов, сколько могут, лишь бы остаток шаблона ещё совпал. В строке utm_source=yandex&utm_medium=cpc&utm_campaign=sale шаблон utm_source=(.+)& дотянется до последнего амперсанда, и в группе окажется yandex&utm_medium=cpc.

Лекарств два. Знак вопроса после квантификатора делает его ленивым: .+? берёт как можно меньше и останавливается на первом амперсанде. Второй способ обычно понятнее: вместо «любой символ» написать «любой, кроме разделителя» — [^&]+. Такой шаблон не зависит от жадности и прямо говорит, где кончается значение.

with landing(query_string) as (
  values ('utm_source=yandex&utm_medium=cpc&utm_campaign=sale')
)
select regexp_replace(query_string, '^utm_source=(.+)&.*$', '\1')    as greedy,
       regexp_replace(query_string, '^utm_source=(.+?)&.*$', '\1')   as lazy,
       regexp_replace(query_string, '^utm_source=([^&]+).*$', '\1')  as negated_class
from landing;

-- yandex&utm_medium=cpc | yandex | yandex

Как экранировать точку, плюс и скобки

Символы . + * ? ( ) [ ] { } ^ $ | и сама обратная косая черта в шаблоне служебные. Чтобы найти такой символ буквально, перед ним ставят \: \. — точка, \+ — плюс, \( — скобка. Второй способ — поместить символ в квадратные скобки: внутри класса точка означает просто точку, [.].

Неэкранированная точка — тихая ошибка. Шаблон суммы ^\d+.\d{2}$ выглядит как «цифры, точка, две цифры», но точка здесь — любой символ, в том числе цифра. Под него подходят и 19x00, и 1900. С \. проходит только 19.00, а класс [.,] допускает оба разделителя.

Одинарная кавычка внутри шаблона удваивается, как в любой строке SQL. Незакрытая скобка — уже ошибка выполнения: PostgreSQL отвечает invalid regular expression: parentheses () not balanced, DuckDB — missing ).

with amounts(raw_amount) as (
  values ('19.00'), ('19x00'), ('1900'), ('19,00')
)
select raw_amount,
       raw_amount ~ '^\d+.\d{2}$'     as dot_any_char,
       raw_amount ~ '^\d+\.\d{2}$'    as dot_escaped,
       raw_amount ~ '^\d+[.,]\d{2}$'  as dot_or_comma
from amounts
order by raw_amount;

-- 19,00 | true | false | true
-- 19.00 | true | true  | true
-- 1900  | true | false | false
-- 19x00 | true | false | false

Чем регулярные выражения в PostgreSQL отличаются от DuckDB

Таблица собрана из запросов, выполненных в PostgreSQL 14 и в DuckDB песочницы. Верхние строки — то, что работает одинаково; на нём построены примеры статьи. Ниже — места, где запрос из одной базы в другой либо падает, либо молча возвращает другое.

Самые опасные строки — те, где ошибки нет. Оператор ~ без якорей, функция regexp_matches и SIMILAR TO выполняются в обоих движках, но означают разное. SIMILAR TO в PostgreSQL — гибрид LIKE и регулярки со знаками % и _, в DuckDB — обычное регулярное выражение на всю строку; в новых запросах проще обойтись без него.

В PostgreSQL начиная с 15-й версии добавлены функции regexp_like, regexp_substr и regexp_count; в 14-й их нет. В других базах, например в MySQL с его оператором REGEXP, названия и умолчания свои — перед переносом запроса сверяйтесь с документацией.

Регулярные выражения и скорость запроса

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

Практических выводов три. Сначала сужайте выборку условиями, которые индекс использовать может, — датой, идентификатором, точным значением, — и только потом применяйте шаблон. Если хватает =, IN или LIKE с фиксированным началом, берите их. А если по одному и тому же шаблону фильтруют постоянно, разберите строку один раз: сохраните домен или метку отдельным столбцом в витрине и фильтруйте по нему. Как читать план и понять, использован ли индекс, — в статье про индексы в SQL.

Частые вопросы

Что попробовать и что читать дальше

Все блоки без пометки «PostgreSQL only» выполняются в песочнице симулятора SQL-аналитика как есть. Для тренировки допишите в учебную вставку свои строки: телефон с добавочным номером, адрес с двумя знаками @, URL, где utm_source стоит дважды, — и посмотрите, какой шаблон первым даст неверный ответ. Потом проверьте event_name ~ 'open' и объясните ноль в результате, не заглядывая в таблицу различий.