Представим, что нам нужно найти всех пользователей, чье имя начинается с букв 'A' или 'B': Abigale, Andy, Brayan, …
Если мы будем использовать только символ %, то ничего не получится, потому что он заменяет собой любой символ. Следовательно, нам нужен более точный механизм фильтрации. Это регулярные выражения, о которых пойдет речь в сегодняшнем уроке.
Регулярные выражения
Язык SQL поддерживает работу с регулярными выражениями (regular expressions или RegEx). Это специальный язык, позволяющий достаточно точно определить шаблоны поиска в строке.
Для работы с регулярными выражениями используется оператор SIMILAR TO вместо LIKE.
Вернемся к нашей задаче — нужно выбрать пользователей, чье имя начинается на 'A' или 'B'. Чтобы решить ее, нужно применить такой шаблон:
SELECT
id,
first_name,
last_name,
email,
birthday
FROM users
WHERE first_name SIMILAR TO '[AB]%';| id | first_name | last_name | birthday | |
|---|---|---|---|---|
8 |
Abigale |
Turner |
Baby_Wintheiser@hotmail.com |
2021-12-06T00:00:00.000Z |
11 |
Alvera |
Bergnaum |
Kendall_Aufderhar@gmail.com |
2021-10-11T00:00:00.000Z |
13 |
Alfreda |
Hermann |
Tyree89@yahoo.com |
2021-12-12T00:00:00.000Z |
16 |
Abe |
Funk |
Dewayne_Lueilwitz69@hotmail.com |
2021-06-16T00:00:00.000Z |
26 |
Andy |
Huel |
Vincenza_Mertz@gmail.com |
2021-07-09T00:00:00.000Z |
28 |
August |
Konopelski |
Karianne_Treutel@gmail.com |
2021-07-04T00:00:00.000Z |
33 |
Alfredo |
Sipes |
Hiram.Schowalter24@hotmail.com |
2022-01-17T00:00:00.000Z |
34 |
Alejandrin |
Nicolas |
Elizabeth58@yahoo.com |
2021-09-15T00:00:00.000Z |
37 |
Alvena |
Rutherford |
Lina59@hotmail.com |
2022-01-22T00:00:00.000Z |
54 |
Brayan |
Senger |
Brianne.OKon75@gmail.com |
2021-06-29T00:00:00.000Z |
59 |
Arne |
Hudson |
Evie_Franey72@gmail.com |
2021-08-17T00:00:00.000Z |
78 |
Angelica |
Donnelly |
Jaylin69@hotmail.com |
2021-07-31T00:00:00.000Z |
85 |
Araceli |
Wisozk |
Ronny.Braun70@hotmail.com |
2021-09-14T00:00:00.000Z |
86 |
Anderson |
Nader |
Darrion_Yost@hotmail.com |
2022-04-30T00:00:00.000Z |
95 |
Andres |
Heidenreich |
Myrna2@gmail.com |
2021-07-11T00:00:00.000Z |
97 |
Bernhard |
Herman |
Vesta.Flatley16@hotmail.com |
2021-12-20T00:00:00.000Z |
View on DB Fiddle
Разберем примененный шаблон [AB]%. В квадратных скобках перечисляются допустимые символы, а далее следует знакомый нам символ %. Этот запрос вернет пользователей с именами Abigale, Andy, Brayan, и так далее.
Представим, что нам нужно найти пользователей, у которых username заканчивается любой буквой.
Это можно сделать таким шаблоном: %[abcdefghijklmnopqrstuvwxyz]. Согласитесь, такая запись неудобна для чтения и записи. А что, если мы случайно пропустим какую-то букву?
В квадратных скобках можно использовать символ - для перечисления. Если записать в квадратных скобках начальный символ, поставить "-" и указать конечный символ, то такой шаблон вернет любой символ из диапазона от начального до конечного.
Напишем запрос, который вернет всех пользователей по условию: username заканчивается любой буквой. Он будет выглядеть так:
SELECT
id,
username,
email,
birthday
FROM users
WHERE username SIMILAR TO '%[a-z]';| id | username | birthday | |
|---|---|---|---|
5 |
Curtis_Baumbach |
Hobart91@hotmail.com |
2021-07-25T00:00:00.000Z |
6 |
Leola.Ward |
Kenya_Legros17@yahoo.com |
2022-02-07T00:00:00.000Z |
8 |
Bernie.Crooks |
Baby_Wintheiser@hotmail.com |
2021-12-06T00:00:00.000Z |
10 |
Berta.Trantow |
Dorian31@gmail.com |
2021-06-15T00:00:00.000Z |
13 |
Tina_Huels |
Tyree89@yahoo.com |
2021-12-12T00:00:00.000Z |
21 |
Connie.Frami |
Audrey.Gibson@yahoo.com |
2022-02-27T00:00:00.000Z |
23 |
Constantin.Heathcote |
Melany_Pfeffer87@hotmail.com |
2021-09-17T00:00:00.000Z |
26 |
Kevon_Howe |
Vincenza_Mertz@gmail.com |
2021-07-09T00:00:00.000Z |
27 |
Ramona.Johnson |
Jasen_DAmore94@gmail.com |
2021-10-09T00:00:00.000Z |
35 |
Prudence_Reichel |
Jaleel.Littel@gmail.com |
2022-02-26T00:00:00.000Z |
36 |
Name_Boyer |
Josie.Mante@yahoo.com |
2021-09-28T00:00:00.000Z |
37 |
Jailyn.Waters |
Lina59@hotmail.com |
2022-01-22T00:00:00.000Z |
38 |
Darron.Mann |
Hailie.McLaughlin64@gmail.com |
2021-08-11T00:00:00.000Z |
39 |
Alfonzo_Lehner |
Crawford64@yahoo.com |
2021-09-29T00:00:00.000Z |
43 |
Kristofer_Lubowitz |
Monserrat.Carter64@hotmail.com |
2022-04-20T00:00:00.000Z |
46 |
Edd_Harris |
Celine.Hand70@yahoo.com |
2022-02-08T00:00:00.000Z |
47 |
Dominique_Lang |
Electa60@gmail.com |
2021-09-15T00:00:00.000Z |
55 |
Berta.Mueller |
Joshua.Lesch24@yahoo.com |
2022-04-14T00:00:00.000Z |
59 |
Sylvan_Nader |
Evie_Franey72@gmail.com |
2021-08-17T00:00:00.000Z |
62 |
Kennedy_Halvorson |
Vladimir.Thiel2@yahoo.com |
2022-02-12T00:00:00.000Z |
65 |
Sean_Reilly |
Dion_Jenkins@gmail.com |
2021-10-29T00:00:00.000Z |
66 |
Trevion.Carter |
Jaunita_Gislason81@yahoo.com |
2022-05-09T00:00:00.000Z |
69 |
Delphine_Bailey |
Sibyl65@yahoo.com |
2021-07-16T00:00:00.000Z |
72 |
Filiberto.Wolf |
Fernando.Lakin@hotmail.com |
2022-03-27T00:00:00.000Z |
73 |
Carter_Skiles |
Zoey_Ziemann@hotmail.com |
2021-08-22T00:00:00.000Z |
74 |
Pasquale.Murphy |
Chance_Moen@gmail.com |
2022-04-01T00:00:00.000Z |
83 |
Declan_Borer |
Celestino81@hotmail.com |
2021-06-25T00:00:00.000Z |
84 |
Verona_Powlowski |
Ashlynn.Lind1@yahoo.com |
2021-09-30T00:00:00.000Z |
87 |
Elmira.Kautzer |
Tommie20@gmail.com |
2021-08-07T00:00:00.000Z |
91 |
Bobbie.Predovic |
Rupert33@hotmail.com |
2022-03-25T00:00:00.000Z |
98 |
Lennie.McGlynn |
Salvador_Hessel83@yahoo.com |
2022-02-06T00:00:00.000Z |
99 |
Eldon_Johns |
Maximillian28@yahoo.com |
2021-12-30T00:00:00.000Z |
View on DB Fiddle
Точно так же можно работать и с русскими буквами: '%[а-я]%'. Такой запрос поможет найти пользователей, у которых в поле username есть русские буквы:
SELECT
id,
first_name,
last_name,
email,
birthday
FROM users
WHERE username SIMILAR TO '%[а-я]%';View on DB Fiddle
Чтобы выбрать все цифры, используем шаблон [0-9]:
SELECT
id,
username,
email,
birthday
FROM users
WHERE username SIMILAR TO '%[0-9]';| id | username | birthday | |
|---|---|---|---|
1 |
Duncan3 |
Trevion53@yahoo.com |
2022-05-25T00:00:00.000Z |
2 |
Michaela11 |
Baylee52@yahoo.com |
2022-01-13T00:00:00.000Z |
3 |
Margarete_Hegmann6 |
Casimer_Cronin@yahoo.com |
2022-04-21T00:00:00.000Z |
4 |
Kayley.Turcotte98 |
Angelita.Altenwerth96@hotmail.com |
2021-10-31T00:00:00.000Z |
7 |
Stan_Sauer4 |
Kiera73@hotmail.com |
2022-04-13T00:00:00.000Z |
9 |
Woodrow14 |
Wyatt9@hotmail.com |
2022-03-05T00:00:00.000Z |
… |
View on DB Fiddle
Такой запрос вернет всех пользователей, чей username заканчивается на любую цифру.
Комбинируя правила, мы можем создавать достаточно сложные шаблоны. Например, проверим, что в поле email введены корректные адреса электронной почты.
Корректная почта должна содержать адрес, который состоит из:
-
Имени с любым количеством любых символов — например,
my_email -
Символа
@ -
Домена с любым количеством любых символов — например,
gmail -
Точки
-
Указания национальной зоны — например,
com
Запрос на поиск корректных адресов будет таким:
SELECT
username,
email
FROM users WHERE email SIMILAR TO '%@%.%';| username | |
|---|---|
Duncan3 |
Trevion53@yahoo.com |
Michaela11 |
Baylee52@yahoo.com |
Margarete_Hegmann6 |
Casimer_Cronin@yahoo.com |
Kayley.Turcotte98 |
Angelita.Altenwerth96@hotmail.com |
Curtis_Baumbach |
Hobart91@hotmail.com |
Leola.Ward |
Kenya_Legros17@yahoo.com |
Stan_Sauer4 |
Kiera73@hotmail.com |
… |
View on DB Fiddle
Такой запрос выведет имена пользователей с корректными адресами электронной почты, однако нам интереснее найти ошибки. В этом случае частица NOT позволит найти строки, которые не соответствуют шаблону:
SELECT
username,
email
FROM users WHERE email NOT SIMILAR TO '%@%.%';View on DB Fiddle
Теперь выберем все адреса электронной почты, у которых национальная зона состоит ровно из двух символов — например, ru, su, io и так далее. При этом исключим зоны, состоящие из трех и более символов — например, com.
Это можно сделать так: %.[a-z][a-z]. Но удобнее воспользоваться еще одним спецсимволом — подчеркиванием _.
Символ подчеркивания обозначает ровно один любой символ, необязательно букву. Наш запрос будет выглядеть так:
SELECT
username,
email
FROM users WHERE email SIMILAR TO '%@%.__';View on DB Fiddle
Чтобы вывести пользователей с адресами электронной почты оканчивающимися на 3 символа, нужно добавить еще одно подчеркивание в наш шаблон:
SELECT
username,
email
FROM users WHERE email SIMILAR TO '%.___';| username | |
|---|---|
Duncan3 |
Trevion53@yahoo.com |
Michaela11 |
Baylee52@yahoo.com |
Margarete_Hegmann6 |
Casimer_Cronin@yahoo.com |
Kayley.Turcotte98 |
Angelita.Altenwerth96@hotmail.com |
Curtis_Baumbach |
Hobart91@hotmail.com |
Leola.Ward |
Kenya_Legros17@yahoo.com |
Stan_Sauer4 |
Kiera73@hotmail.com |
View on DB Fiddle
Мы рассмотрели наиболее полезные и часто используемые возможности регулярных выражений в SQL, но они ими не ограничиваются. Полный перечень возможностей вы можете посмотреть в документации.
Выводы
В этом уроке мы познакомились с оператором SIMILAR TO. Он помогает формировать шаблоны для фильтрации текстовых полей. Повторим самые полезные и часто используемые спецсимволы:
-
Символ
%соответствует любому количеству любых символов -
Квадратные скобки
[]используются для перечисления конкретных символов — например,[АЕИОУЭЮ] -
Дефис
-в квадратных скобках означает перечисление символов — например,[0-9]соответствует любой цифре -
Подчеркивание
_означает ровно один любой символ (букву или цифру)
Дополнительные материалы
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты
