REGEXP_REPLACE в SQL: замена по шаблону, флаги g и i, обратные ссылки

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

REGEXP_REPLACE отличается от обычного REPLACE тем, что ищет не фиксированную подстроку, а целый класс совпадений по шаблону, поэтому одним вызовом можно убрать все нецифровые символы, схлопнуть любые пробельные последовательности или вырезать управляющие символы. В PostgreSQL функция работает с диалектом POSIX-регулярных выражений и принимает шаблон, замену и необязательную строку флагов. Дальше разберём её сигнатуру, флаги g, i и многострочный режим, обратные ссылки на группы, расхождения POSIX и PCRE, а также аналоги в MySQL и ClickHouse.

Сигнатура и базовая замена

В PostgreSQL функция выглядит так: REGEXP_REPLACE(source, pattern, replacement [, flags]). По умолчанию заменяется только первое совпадение, поэтому почти всегда нужен флаг 'g' (global).

SELECT
  REGEXP_REPLACE('+1 (555) 123-45-67', '[^0-9]', '', 'g') AS digits,
  REGEXP_REPLACE('hello   world', '\s+', ' ', 'g')        AS one_space;
-- 15551234567 | hello world

Ключевые моменты:

• Без 'g' уйдёт только первое совпадение: REGEXP_REPLACE('a-b-c', '-', '+') даст a+b-c.
• Класс [^0-9] означает «любой символ, кроме цифры» — классический способ оставить только цифры.
• Пустая строка '' в качестве замены просто вырезает совпадения, что удобно для очистки.
• Если шаблон ни с чем не совпал, исходная строка возвращается без изменений, а не NULL.

Флаги g, i и многострочный режим

Флаги передаются строкой четвёртым аргументом и комбинируются: 'gi' — это сразу global и без учёта регистра.

SELECT
  REGEXP_REPLACE(email, 'GMAIL', 'gmail', 'gi') AS norm,
  REGEXP_REPLACE(name, '\s+', ' ', 'g')         AS clean_name
FROM users;

• g — заменять все вхождения, а не только первое.
• i — без учёта регистра, Gmail, GMAIL и gmail совпадут одинаково.
• n (или m) — многострочный режим: ^ и $ начинают цепляться за границы строк внутри текста.

Обратные ссылки в замене

В шаблон поля собирают в группы круглыми скобками (...), а в строке замены на них ссылаются как \1, \2 и так далее. Это позволяет переставлять и переформатировать куски строки.

SELECT
  REGEXP_REPLACE(name, '^(\w+)\s+(\w+)$', '\2, \1') AS last_first
FROM employees;
-- 'Ada Lovelace' -> 'Lovelace, Ada'

Ещё пример — нормализуем телефон в единый формат, выдёргивая группы цифр:

SELECT
  REGEXP_REPLACE('5551234567', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS pretty;
-- (555) 123-4567

Обратная ссылка \1 указывает на текст, захваченный первой группой, поэтому порядок скобок важен. Сама \& подставляет всё совпадение целиком, что бывает удобно, когда нужно обернуть найденное, а не переставить части.

POSIX против PCRE: главная ловушка

PostgreSQL использует диалект POSIX ARE, а MySQL и многие языки — PCRE. Выглядят они похоже, но детали кусаются.

• В POSIX (PostgreSQL) для буквенно-цифровых символов используйте [[:alnum:]] или короткие \w, \d, \s. Перлоподобные \b (граница слова) есть, но поведение по краям отличается.
• Ленивые квантификаторы *? и +? в PostgreSQL поддерживаются, а вот именованные группы и lookbehind — нет.
• Самая частая ошибка: забыть 'g' и удивляться, почему заменилось только одно вхождение.

-- GOTCHA: without 'g' only the first space collapses
SELECT REGEXP_REPLACE('a  b  c', '\s+', '_');     -- a_b  c
SELECT REGEXP_REPLACE('a  b  c', '\s+', '_', 'g'); -- a_b_c

Различия в MySQL и ClickHouse

Функция есть не везде и ведёт себя по-разному.

• MySQL 8+ имеет REGEXP_REPLACE(source, pattern, replacement [, pos, occurrence, match_type]). Здесь нет строки флагов как в Postgres: глобальная замена идёт по умолчанию (когда occurrence = 0), а регистр и многострочность задаются через match_type вроде 'i' или 'm'. Обратные ссылки пишутся как $1, а не \1.

SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits FROM users;
SELECT REGEXP_REPLACE(name, '^(\\w+) (\\w+)$', '$2, $1') AS last_first FROM employees;

• ClickHouse разделяет replaceRegexpOne (первое вхождение) и replaceRegexpAll (все вхождения), а ссылки на группы пишутся как \1.

SELECT replaceRegexpAll(phone, '[^0-9]', '') AS digits FROM users;

При переносе одной и той же чистки между PostgreSQL, MySQL и ClickHouse спотыкаются на трёх вещах: в Postgres глобальность включает флаг 'g', в MySQL — нулевой occurrence, а в ClickHouse выбор между replaceRegexpOne и replaceRegexpAll делает само имя функции. Стиль обратных ссылок тоже расходится: \1 в PostgreSQL и ClickHouse против $1 в MySQL. Наконец, экранирование в строковых литералах MySQL обычно требует двойного слэша (\\w), тогда как POSIX-классы вроде [[:alnum:]] понимает только PostgreSQL.

Если REGEXP_REPLACE стоит на входе пайплайна и чистит телефоны, идентификаторы или CSV-поля до аналитики, добавьте к шаблону пару строк ввода и ожидаемого вывода: на пустой строке функция вернёт пустую строку, на отсутствии совпадений — исходный текст без изменений, а не NULL. Для больших таблиц помните, что REGEXP_REPLACE по колонке в WHERE или JOIN не использует обычный B-tree-индекс — под такие фильтры в PostgreSQL заводят функциональный индекс по самому выражению.

Суть во всех трёх СУБД одна: шаблон, замена и признак глобальности. Меняются лишь имя функции, способ задать глобальность и многострочность да стиль обратных ссылок (\1 против $1).