Привет, Хабр!
Представьте, что вам нужно найти иголку в стоге сена, но стог — это ваша БД, а иголка — данные со сложным шаблоном. Дефолтные операторы 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-запросам отличаются и пересекаются. На практике попробуете написать запросы с разных позиций и разберетесь, как эффективно работать вместе, чтобы достигать общих целей. Урок поможет вам понять, как создать эффективное взаимодействие между аналитиками и разработчиками. Зарегистрироваться.
ссылка на оригинал статьи https://habr.com/ru/articles/851942/
Добавить комментарий