Регулярные выражения как способ решения задач в SQL
⏱ Время чтения текста – 7 минутИспользование регулярных выражений для выбора определенных ячеек таблицы используется в SQL не так часто, как могло бы. И очень зря — этот инструмент легко позволяет найти в таблице нужные значения, так как использует шаблон для поиска последовательности метасимволов в тексте. Такие задачи встречаются как в различных тренажерах или на собеседованиях на позиции аналитика, так и в реальной практике аналитиков, которые работают в базах SQL. Подобные шаблоны для поиска определенных элементов и последовательностей в тексте используются в самых разных областях. Например, на многих сайтах существует проверка email-адреса, который вы вводите при регистрации, на соответствие стандартному шаблону.
Как это сделать, мы разберёмся постепенно, а пока давайте начнем с самого начала: с определения.
Что такое «регулярное выражение»?
Регулярное выражение — последовательность букв и/или символов, которая может встречаться в слове. Например, есть достаточно простое регулярное выражение “bat”. Оно читается как буква b, за которой следует буква a и t, и этому шаблону соответствуют такие слова, как, bat, combat и batalion.
Давайте разберем несколько типовых задачек, чтобы вам было понятнее, как правильно работать с регулярными выражениями в SQL. Для решения всех задач, которые мы сегодня рассмотрим, мы будем использовать функцию regexp_matches(), которая будет сравнивать значения в ячейках с шаблоном, который задается внутри этой функции.
Количество гласных букв в выражении
Итак, предположим, вам нужно посчитать количество гласных букв в каждой ячейке определенного столбца таблицы. Именно для такой задачи и нужны регулярные выражения. Код, который приведен ниже (вы можете легко его прогнать в своем SQL), на простом примере показывает, как легко решить эту задачу. В результате, мы получаем еще одну колонку Count, в которой хранится искомая информация.
with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler'))
as map(id, source_type))
select source_type, count(1) from (
select *, regexp_matches(source_type,'([aeiou])','g') as pattern from example_table ) as t
group by source_type
Количество согласных букв в выражении
Если мы хотим решить обратную задачу, то можно подойти к решению двумя способами. Первый способ — аналогично предыдущему можно перечислить все согласные буквы английского алфавита в квадратных скобках. Но почему бы не решить задачу элегантнее? Для этого есть второй способ — использовать отрицание, то есть посчитать количество всех букв, которые не являются гласными. Для этого используется оператор ^.
with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler'))
as map(id, source_type))
select source_type, count(1) from (
select *, regexp_matches(source_type,'([^aeiou])','g') as pattern from example_table ) as t
group by source_type
Количество цифр в выражении равно 3
Если нужно найти конкретное число определенных символов в выражении, то в конце запроса нужно указать оператор HAVING.
with example_table as (select * from (values (1, '1a2s3d'), (2, 'qw12e'), (3, 'q56we1651qwe'), (4, 'qw4e2'))
as map(id, source_type))
select source_type, COUNT(*) from (
select *, regexp_matches(source_type,'\d','g') as pattern from example_table ) as t
GROUP BY source_type
HAVING COUNT(*) = 3
В номере телефона есть два дефиса
Теперь давайте перейдем к более конкретным запросам, которые могут пригодиться в реальной практике. Например, у аналитика может стоять задача найти все номера телефона, в которых присутствует два или более дефисов.
В первом блоке кода мы создаем тестовую таблицу, затем считаем количество дефисов в каждой ячейке (ячейки без дефисов не включаются в финальную таблицу), а после этого проставляем значения True/False относительно условия на количество дефисов. Сделать это можно с помощью оператора CASE WHEN COUNT ().
with example_table as (
select * from (
values
(1, '8931-123-456'),
(2, '8931123-456'),
(3, '+7812123456'),
(4, '8-931-123-42-24')
)
as map(id, source_type))
select source_type, CASE WHEN COUNT(1) >= 2 THEN 'True' ELSE 'False' END from (
select *, regexp_matches(source_type,'-','g') as pattern from example_table ) as t
GROUP BY 1
Все имена, которые написаны с большой буквы
Тут мы уже приступаем к задаче посложнее: нужно найти имена людей, которые написаны с заглавной буквы со всем датасете. Для этого нам нужно найти все значения, подходящие под заданный шаблон: первая буква слова — заглавная.
with example_table as (
select * from (
values
(1, 'alex'),
(2, 'Alex'),
(3, 'Vasya'),
(4, 'petya')
)
as map(id, source_type))
select source_type from (
select *, regexp_matches(source_type,'^[A-Z]','g') as pattern from example_table ) as t
GROUP BY 1
Вывести номера телефонов, которые попадают под паттерн +71234564578
Последней задачей мы разберем поиск телефонных номеров в списке. Для этого нам нужно найти те значения, которые начинаются со знака “+”, затем идет цифра 7 и 10 любых цифр после этого.
with example_table as (
select * from (
values
(1, '+7(931)1234546'),
(2, '+79312991809'),
(3, '89311234565'),
(4, '244-02-38')
)
as map(id, source_type))
select source_type from (
select *, regexp_matches(source_type,'^\+7[0-9]{10}','g') as pattern from example_table ) as t
GROUP BY 1
Вывести все настоящие email-адреса
Как мы говорили в начале, регулярные выражения могут использоваться для таких задач как поиск сложных выражений по определённому шаблону. На самом деле, ничего особенного в такой задаче нет — главное, грамотно сформировать шаблон выражения и дело в шляпе!
with example_table as (
select * from (
values
(1, 'email.asd@ya.ru'),
(2, 'something@new.ru'),
(3, '@ya.ru'),
(4, 'asdasd'),
(5, '_asdasdasd@mail.ru'),
(6, 'asd_asdas@mail.ru'),
(7, '.asdasd@mail.ru'),
(8, '007asd@email.com')
) as map(id, source_type)
)
select source_type
from (
select source_type, regexp_matches(source_type, '^[^_.0-9][a-z0-9._]+@[a-z]+\.[a-z]+$')
from example_table ) as t
Использование регулярных выражений может помочь легко и просто решить достаточно трудные задачи. Пишите в комментариях, если у вас есть какая-то задача по поиску определенных шаблонов в тексте, которая вам никак не дается. Попробуем решить её вместе!