Памятка по регулярным выражениям в SQL

Памятка по регулярным выражениям в SQL

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

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

Говоря о регулярных выражениях в SQL, первым вопросом, что приходит на ум, вероятно, будет:  «Зачем они нужны, ведь есть же оператор LIKE и функции REPLACE, SUBSTR?». Да, для широкого спектра штатных ситуаций этих возможностей более чем достаточно, но возможности регулярных выражений, в т.ч. поддержка символьных классов POSIX, значительно помогают в решении нетривиальных задач.

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

Функции регулярных выражений в SQL

Название Краткое описание
REGEXP_LIKE Выражение используется в конструкции WHERE. Является расширением функционала описания LIKE
REGEXP_COUNT Данная функция подсчитывает вхождение шаблона в строку
REGEXP_INSTR Данная функция расширяет возможности функции INSTR. Возвращает местоположение шаблона регулярного выражения в строке
REGEXP_REPLACE Расширение функции REPLACE. Позволяет модифицировать строку с использованием шаблонов регулярных выражений
REGEXP_SUBSTR Расширение функции SUBSTR. С помощью нее можно извлечь подстроку из строки с использованием шаблонов регулярных выражений.

 

Синтаксис шаблонов регулярных выражений

 

Метасимволы, квантификаторы и операторы привязки

Символ Описание
^ Привязка выражения к началу строки
$ Привязка выражения к концу строки
* Символ встречается 0 или более раз
? Символ  встречается 0 или 1 раз
+ Символ  встречается 1 и более раз
{m} Встречается ровно m раз
{m,} Встречается минимум m раз
{m, n} Встречается минимум m раз, но не более n раз
/ Разделяет альтернативные варианты, часто используется с оператором группировки ()
( ) Группирует подвыражения для альтернативы, квантификатора или ссылочности
[char] Обозначает список символов; большинство метасимволов в списке символов представляют собой литеры, за исключением символьных классов и метасимволов ^ и —

Символьные классы POSIX

Класс символов Описание
[:alpha:] Буквы
[:lower:] Буквы в нижнем регистре
[:upper:] Буквы в верхнем регистре
[:digit:] Цифры
[:alnum:] Буквы и цифры
[:space:] Пробелы (не печатаемые символы), такие как перевод каретки, новая строка, вертикальная табуляция и подача страницы
[:punct:] Знаки препинания
[:cntrl:] Управляющие символы (не печатаемые)
[:print:] Печатаемые символы

 

Примеры использования регулярных выражений

Ниже приведены примеры использования регулярных выражений в SQL. По сути, данные примеры являются решением задач по SQL на codewars.com и демо-запросами к БД Битрикса (несмотря на то, что у MySQL несколько иной синтаксис регулярок).

Использование POSIX

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

SELECT
project AS project,
REGEXP_REPLACE(address,'[[:digit:]]','','g') AS letters,
REGEXP_REPLACE(address,'[[:alpha:]]','','g') AS numbers
FROM 
repositories

Прочие шаблоны:

Выбор всех мейлов пользователей, заканчивающихся на .ru:

SELECT
*
FROM
b_user
WHERE 
REGEXP_LIKE((email, '(*).ru$')

Подсчет вхождения шаблона в строку:

SELECT 
REGEXP_COUNT('28128128128281281', '(28)1', 1, 'i') AS test_regex
FROM test;

Первое вхождение символа ‘д’ в строке:

SELECT 
REGEXP_INSTR (last_name, 'д', 1, 1, 0, 'i') AS first_d
FROM b_user;

Поиск первого двухзначного числа в строке:

SELECT REGEXP_SUBSTR (phone, '(\d)(\d)')
FROM b_user;

В целом все, на этом статья завершается. Однако, стоит заметить, что это не последняя статья по теме. В ближайшие месяцы точно будут аналогичные статьи по регуляркам в MySQL (т.к. несколько другой синтаксис), подробные статьи по регуляркам в php и регуляркам в python’е.