Ниже рассматриваются возможности использования регулярных выражений для анализа данных, на примере заданий, с которыми автор сталкивался на codewars и иных ситуациях.
Говоря о регулярных выражениях в 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’е.