Регулярные выражения в SQL19.10.2024 21:45

c99ea60676b0f51005d25a5f0f0afef4.jpg

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

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

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

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

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

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

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

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

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

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

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

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

Допустим, есть таблица 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 числовой.

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

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

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

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

В PostgreSQL: 

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

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

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

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-запросам отличаются и пересекаются. На практике попробуете написать запросы с разных позиций и разберетесь, как эффективно работать вместе, чтобы достигать общих целей. Урок поможет вам понять, как создать эффективное взаимодействие между аналитиками и разработчиками. Зарегистрироваться.

© Habrahabr.ru