Регулярные выражения в SQL

Привет, Хабр!

Представьте, что вам нужно найти иголку в стоге сена, но стог — это ваша БД, а иголка — данные со сложным шаблоном. Дефолтные операторы LIKE и IN тут не помогут — слишком уж они прямолинейны. Но зато здесь отлично зайдут регулярные выражения, которые позволяют выполнять сложные поиски и преобразования строк.

Основные функции для работы с регулярками

Основные функции для работы с регулярными выражениями в SQL:

• REGEXP_LIKE — проверяет, соответствует ли строка заданному шаблону.

• REGEXP_REPLACE — заменяет часть строки, соответствующую шаблону.

• REGEXP_SUBSTR — извлекает подстроку по шаблону.

• REGEXP_INSTR — находит позицию вхождения шаблона.

• REGEXP_COUNT — считает количество вхождений шаблона.

• SIMMILAR TO — проверяет, соответствует ли строка определённому шаблону.

Начнем сразу с практического применения этих замечательных функций.

Практические сценарии

Валидация электронных адресов

Допустим, есть таблица users с полем email, и есть подозрение, что не все адреса введены корректно. Нужно выбрать все записи, где email не соответствует стандартному формату.

Используем REGEXP_LIKE для проверки формата email:

SELECT user_id, email
FROM users
WHERE NOT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Разбор:

• ^[A-Za-z0-9._%+-]+ — начало строки, допустимые символы перед @.

• @[A-Za-z0-9.-]+ — символ @ и доменная часть.

• \.[A-Za-z]{2,}$ — точка и домен верхнего уровня из минимум 2 букв.

• NOT REGEXP_LIKE — выбираем записи, которые не соответствуют шаблону.

В итоге мы получим список пользователей с некорректными email, которым можно отправить уведомление о необходимости обновить контактную информацию.

Стандартизация номеров телефонов

В таблице contacts номера телефонов хранятся в разных форматах: с пробелами, скобками, тире и даже без кода страны. Необходимо преобразовать все номера к единому формату +7XXXXXXXXXX.

Используем REGEXP_REPLACE для удаления лишних символов и добавления кода страны:

UPDATE contacts
SET phone_number = '+7' || REGEXP_REPLACE(phone_number, '\D', '')
WHERE REGEXP_LIKE(phone_number, '^\+?7?\d{10}$');

Разбор:

• REGEXP_REPLACE(phone_number, '\D', '') — удаляем все нецифровые символы.

• '+7' || — добавляем код страны в начале.

• ^\+?7?\d{10}$ — выбираем номера, которые уже могут начинаться с +7 или 7, чтобы избежать дублей кода страны.

На выходе получим единообразные номера телефонов, с которыми легче работать и которые можно использовать для автоматического набора или отправки SMS.

Поиск специфичных паттернов в логах

Есть таблица system_logs с полем log_entry, и нужно найти все записи, где произошла ошибка доступа для пользователей с определенным шаблоном имени, например, начинающихся с admin_ и заканчивающихся цифрами.

Используем REGEXP_LIKE для поиска соответствующих записей:

SELECT log_id, log_entry
FROM system_logs
WHERE REGEXP_LIKE(log_entry, 'Access denied for user \'admin_\w*\d+\'', 'i');

Разбор:

• Access denied for user \'admin_\w*\d+\' — ищем строки с сообщением об отказе в доступе для пользователя, имя которого соответствует шаблону.

• \' — экранируем одинарные кавычки.

• \w* — любое количество буквенных символов.

• \d+ — один или более цифр.

• 'i' — флаг нечувствительности к регистру.

На выходе получаем список логов с попытками доступа подозрительных пользователей.

Маскировка персональных данных

В целях безопасности нужно замаскировать номера кредитных карт в таблице payments, оставив видимыми только последние 4 цифры.

Используем функцию REGEXP_REPLACE для замены части строки на символы *:

SELECT
    payment_id,
    REGEXP_REPLACE(card_number, '\d{12}(\d{4})', '************\1') AS masked_card_number
FROM payments;

Разбор:

• \d{12}(\d{4}) — ищем первые 12 цифр и захватываем последние 4 цифры.

• '************\1' — заменяем первые 12 цифр на *, а последние 4 цифры оставляем (ссылка на первую захватывающую группу \1).

Получим полеmasked_card_number, где номера карт выглядят как ************1234. Это важно для соответствия требованиям безопасности и сохранения конфиденциальности клиентов.

Извлечение хэштегов из сообщений

В таблице social_posts есть поле content, содержащее текст сообщений с хэштегами. Нужно извлечь все уникальные хэштеги из этих сообщений.

Используем комбинацию функций REGEXP_SUBSTR и рекурсивного запроса:

WITH hashtags AS (
  SELECT
    post_id,
    REGEXP_SUBSTR(content, '#\w+', 1, LEVEL) AS hashtag
  FROM social_posts
  CONNECT BY REGEXP_SUBSTR(content, '#\w+', 1, LEVEL) IS NOT NULL
  AND PRIOR post_id = post_id
  AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT DISTINCT hashtag
FROM hashtags
WHERE hashtag IS NOT NULL;

Разбор:

• #\w+ — ищем слова, начинающиеся с #.

• LEVEL — используем для извлечения каждого последующего вхождения.

• CONNECT BY — рекурсивно проходим по каждому сообщению и извлекаем все хэштеги.

• DISTINCT — оставляем только уникальные хэштеги.

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

Разбиение CSV-строки на элементы

В таблице orders есть поле product_ids, содержащее идентификаторы продуктов в формате CSV (например, 1,2,3,4). Нужно создать запись для каждого продукта в заказе.

Используем REGEXP_SUBSTR вместе с рекурсией:

SELECT
    order_id,
    TO_NUMBER(REGEXP_SUBSTR(product_ids, '[^,]+', 1, LEVEL)) AS product_id
FROM orders
CONNECT BY REGEXP_SUBSTR(product_ids, '[^,]+', 1, LEVEL) IS NOT NULL
AND PRIOR order_id = order_id
AND PRIOR SYS_GUID() IS NOT NULL;

Разбор:

• [^,]+ — захватываем последовательности символов, не являющихся запятой.

• LEVEL — используем для прохода по каждому элементу CSV.

• TO_NUMBER — преобразуем строку в число, если product_id числовой.

На выходе получаем список заказов, где каждая строка соответствует одному продукту из заказа.

Нюансы использования регулярных выражений в разных СУБД

Oracle, MySQL, PostgreSQL

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

Oracle

Oracle имеет полный набор функций для работы с регулярками:

• REGEXP_LIKE

• REGEXP_REPLACE

• REGEXP_SUBSTR

• REGEXP_INSTR

• REGEXP_COUNT

MySQL

В MySQL поддержка регулярных выражений зависит от версии:

• До версии 8.0 использовался оператор REGEXP или RLIKE для проверки соответствия.

• В версии 8.0 и выше добавлены функции REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR.

PostgreSQL

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

• Оператор ~ и ~* для проверки соответствия (чувствительный и нечувствительный к регистру).

• Функции regexp_replace, regexp_matches, regexp_split_to_table, regexp_split_to_array.

Синтаксис основан на расширениях POSIX (как и mysql), но с доп. возможностями.

Нюансы синтаксиса

Обратите внимание на различия в экранировании символов и использовании спец. последовательностей:

• В Oracle нужно удваивать обратные слэши \\ в некоторых случаях.

• В MySQL и PostgreSQL достаточно одного обратного слэша \.

Пример:

В Oracle:

SELECT REGEXP_REPLACE('ABC123', '[A-Z]+', '') FROM dual;
-- Результат: 123

Чтобы использовать специальные последовательности, например, \d для цифр, в Oracle нужно удваивать обратные слэши:

SELECT REGEXP_REPLACE('ABC123', '\\d+', '') FROM dual;
-- Результат: ABC

В PostgreSQL:

SELECT regexp_replace('ABC123', '\d+', '', 'g');
-- Результат: ABC

Разбор:

• Oracle: Требует удваивания обратного слэша \\d, чтобы корректно интерпретировать специальную последовательность \d (цифра).

• PostgreSQL и MySQL: Достаточно одного обратного слэша \d.

Различные техники

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

SELECT column_name
FROM table_name
WHERE REGEXP_LIKE(column_name, '(.*)\1');

(.*)\1 — захватываем любую последовательность символов и ищем её повторение сразу после.

Некоторые СУБД позволяют использовать условные конструкции внутри регулярных выражений.

Пример (Oracle):

SELECT REGEXP_SUBSTR('abc123xyz', '(abc|123|xyz)', 1, 2) FROM dual;
-- Результат: 123

(abc|123|xyz) — используем оператор | для указания альтернатив. Параметр 2 в конце функции указывает, что мы хотим получить второе совпадение.

По дефлоту квантификаторы в регулярных выражениях "жадные", т.е они захватывают максимально возможное количество символов.

Пример:

SELECT REGEXP_SUBSTR('abcccccd', 'abc*') FROM dual;
-- Результат: abccccc

Если мы хотим сделать квантификатор "ленивым", то добавляем ? после него:

SELECT REGEXP_SUBSTR('abcccccd', 'abc*?') FROM dual;
-- Результат: abc

Разбор:

• c* — жадный квантификатор, захватывает все c.

• c*? — ленивый квантификатор, захватывает минимальное количество c.

Производительность

Регулярные выражения — инструмент мощный, но ресурсоёмкий.

Советы:

• Индексы не работают с регулярками. Если возможно, используйте дополнительные условия в WHERE, которые могут задействовать индексы.

• Ограничивайте выборку. Используйте доп. фильтры, чтобы сократить количество обрабатываемых строк.

• Кэшируйте результаты. Если регулярное выражение используется часто и результаты редко меняются, рассмотрите возможность кэширования.

---

Заключение

Регулярные выражения позволяют выполнять сложные операции, которые иначе потребовали бы дополнительных шагов или иногда скриптов.

Приглашаем вас пройти вступительное тестирование курса «SQL для разработчиков и аналитиков». Этот курс поможет вам освоить ключевые инструменты работы с базами данных и научит эффективно использовать SQL для анализа данных.

Также рекомендуем обратить внимание на каталог курсов в разделе «Аналитика и анализ», где собраны программы, направленные на развитие навыков работы с данными и их интерпретации.