![](\"http:\/\/test.leftjoin.ru\/pictures\/email.png.jpg\")
<\/p>\nИспользование регулярных выражений для выбора определенных ячеек таблицы используется в SQL не так часто, как могло бы. И очень зря — этот инструмент легко позволяет найти в таблице нужные значения, так как использует шаблон для поиска последовательности метасимволов в тексте. Такие задачи встречаются как в различных тренажерах или на собеседованиях на позиции аналитика, так и в реальной практике аналитиков, которые работают в базах SQL. Подобные шаблоны для поиска определенных элементов и последовательностей в тексте используются в самых разных областях. Например, на многих сайтах существует проверка email-адреса, который вы вводите при регистрации, на соответствие стандартному шаблону.<\/p>\n
<\/p>\n
Как это сделать, мы разберёмся постепенно, а пока давайте начнем с самого начала: с определения.<\/p>\n
Что такое «регулярное выражение»?<\/i>
\nРегулярное выражение —<\/b> последовательность букв и\/или символов, которая может встречаться в слове. Например, есть достаточно простое регулярное выражение “bat”. Оно читается как буква b, за которой следует буква a и t, и этому шаблону соответствуют такие слова, как, bat, combat и batalion.
\nДавайте разберем несколько типовых задачек, чтобы вам было понятнее, как правильно работать с регулярными выражениями в SQL. Для решения всех задач, которые мы сегодня рассмотрим, мы будем использовать функцию regexp_matches(), которая будет сравнивать значения в ячейках с шаблоном, который задается внутри этой функции.<\/p>\nКоличество гласных букв в выражении<\/h2>\n
Итак, предположим, вам нужно посчитать количество гласных букв в каждой ячейке определенного столбца таблицы. Именно для такой задачи и нужны регулярные выражения. Код, который приведен ниже (вы можете легко его прогнать в своем SQL), на простом примере показывает, как легко решить эту задачу. В результате, мы получаем еще одну колонку Count, в которой хранится искомая информация.<\/p>\n
with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) \r\nas map(id, source_type))\r\n\r\nselect source_type, count(1) from (\r\nselect *, regexp_matches(source_type,'([aeiou])','g') as pattern from example_table ) as t\r\ngroup by source_type<\/code><\/pre>Количество согласных букв в выражении<\/h2>\n
Если мы хотим решить обратную задачу, то можно подойти к решению двумя способами. Первый способ — аналогично предыдущему можно перечислить все согласные буквы английского алфавита в квадратных скобках. Но почему бы не решить задачу элегантнее? Для этого есть второй способ — использовать отрицание, то есть посчитать количество всех букв, которые не являются гласными. Для этого используется оператор ^.<\/p>\n
with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) \r\nas map(id, source_type))\r\n\r\nselect source_type, count(1) from (\r\nselect *, regexp_matches(source_type,'([^aeiou])','g') as pattern from example_table ) as t\r\ngroup by source_type<\/code><\/pre>Количество цифр в выражении равно 3<\/h2>\n
Если нужно найти конкретное число определенных символов в выражении, то в конце запроса нужно указать оператор HAVING.<\/p>\n
with example_table as (select * from (values (1, '1a2s3d'), (2, 'qw12e'), (3, 'q56we1651qwe'), (4, 'qw4e2')) \r\nas map(id, source_type))\r\n\r\nselect source_type, COUNT(*) from (\r\nselect *, regexp_matches(source_type,'\\d','g') as pattern from example_table ) as t\r\nGROUP BY source_type\r\nHAVING COUNT(*) = 3<\/code><\/pre>В номере телефона есть два дефиса<\/h2>\n
Теперь давайте перейдем к более конкретным запросам, которые могут пригодиться в реальной практике. Например, у аналитика может стоять задача найти все номера телефона, в которых присутствует два или более дефисов.
\nВ первом блоке кода мы создаем тестовую таблицу, затем считаем количество дефисов в каждой ячейке (ячейки без дефисов не включаются в финальную таблицу), а после этого проставляем значения True\/False относительно условия на количество дефисов. Сделать это можно с помощью оператора CASE WHEN COUNT ().<\/p>\n
with example_table as (\r\n select * from (\r\n values \r\n (1, '8931-123-456'), \r\n (2, '8931123-456'), \r\n (3, '+7812123456'), \r\n (4, '8-931-123-42-24')\r\n )\r\nas map(id, source_type))\r\n\r\nselect source_type, CASE WHEN COUNT(1) >= 2 THEN 'True' ELSE 'False' END from (\r\nselect *, regexp_matches(source_type,'-','g') as pattern from example_table ) as t\r\nGROUP BY 1<\/code><\/pre>Все имена, которые написаны с большой буквы<\/h2>\n
Тут мы уже приступаем к задаче посложнее: нужно найти имена людей, которые написаны с заглавной буквы со всем датасете. Для этого нам нужно найти все значения, подходящие под заданный шаблон: первая буква слова — заглавная.<\/p>\n
with example_table as (\r\n select * from (\r\n values \r\n (1, 'alex'), \r\n (2, 'Alex'), \r\n (3, 'Vasya'), \r\n (4, 'petya')\r\n )\r\nas map(id, source_type))\r\n\r\nselect source_type from (\r\nselect *, regexp_matches(source_type,'^[A-Z]','g') as pattern from example_table ) as t\r\nGROUP BY 1<\/code><\/pre>Вывести номера телефонов, которые попадают под паттерн +71234564578<\/h2>\n
Последней задачей мы разберем поиск телефонных номеров в списке. Для этого нам нужно найти те значения, которые начинаются со знака “+”, затем идет цифра 7 и 10 любых цифр после этого.<\/p>\n
with example_table as (\r\n select * from (\r\n values \r\n (1, '+7(931)1234546'), \r\n (2, '+79312991809'), \r\n (3, '89311234565'), \r\n (4, '244-02-38')\r\n )\r\nas map(id, source_type))\r\n\r\nselect source_type from (\r\nselect *, regexp_matches(source_type,'^\\+7[0-9]{10}','g') as pattern from example_table ) as t\r\nGROUP BY 1<\/code><\/pre>Вывести все настоящие email-адреса<\/h2>\n
Как мы говорили в начале, регулярные выражения могут использоваться для таких задач как поиск сложных выражений по определённому шаблону. На самом деле, ничего особенного в такой задаче нет — главное, грамотно сформировать шаблон выражения и дело в шляпе!<\/p>\n
with example_table as (\r\n select * from (\r\n values\r\n (1, 'email.asd@ya.ru'),\r\n (2, 'something@new.ru'),\r\n (3, '@ya.ru'),\r\n (4, 'asdasd'),\r\n (5, '_asdasdasd@mail.ru'),\r\n (6, 'asd_asdas@mail.ru'),\r\n (7, '.asdasd@mail.ru'),\r\n (8, '007asd@email.com')\r\n ) as map(id, source_type)\r\n)\r\n\r\nselect source_type \r\nfrom (\r\n select source_type, regexp_matches(source_type, '^[^_.0-9][a-z0-9._]+@[a-z]+\\.[a-z]+$')\r\n from example_table ) as t<\/code><\/pre>Использование регулярных выражений может помочь легко и просто решить достаточно трудные задачи. Пишите в комментариях, если у вас есть какая-то задача по поиску определенных шаблонов в тексте, которая вам никак не дается. Попробуем решить её вместе!<\/p>\n",
"date_published": "2022-01-17T16:48:16+03:00",
"date_modified": "2022-01-13T14:24:01+03:00",
"image": "http:\/\/test.leftjoin.ru\/pictures\/email.png.jpg",
"_date_published_rfc2822": "Mon, 17 Jan 2022 16:48:16 +0300",
"_rss_guid_is_permalink": "false",
"_rss_guid": "127",
"_e2_data": {
"is_favourite": false,
"links_required": [
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css"
],
"og_images": [
"http:\/\/test.leftjoin.ru\/pictures\/email.png.jpg"
]
}
}
],
"_e2_version": 3365,
"_e2_ua_string": "E2 (v3365; Aegea)"
}