Представим, что нам нужно найти всех пользователей, чье имя начинается с букв '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 | 2021-12-06T00:00:00.000Z | |
11 | Alvera | Bergnaum | 2021-10-11T00:00:00.000Z | |
13 | Alfreda | Hermann | Tyree89@yahoo.com | 2021-12-12T00:00:00.000Z |
16 | Abe | Funk | 2021-06-16T00:00:00.000Z | |
26 | Andy | Huel | 2021-07-09T00:00:00.000Z | |
28 | August | Konopelski | 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 | 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 | 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 | 2022-02-07T00:00:00.000Z | |
8 | Bernie.Crooks | 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 | 2021-09-17T00:00:00.000Z | |
26 | Kevon_Howe | 2021-07-09T00:00:00.000Z | |
27 | Ramona.Johnson | 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 | 2021-08-17T00:00:00.000Z | |
62 | Kennedy_Halvorson | Vladimir.Thiel2@yahoo.com | 2022-02-12T00:00:00.000Z |
65 | Sean_Reilly | 2021-10-29T00:00:00.000Z | |
66 | Trevion.Carter | 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 | 2021-08-22T00:00:00.000Z | |
74 | Pasquale.Murphy | 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 | 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 | 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 | |
Kayley.Turcotte98 | Angelita.Altenwerth96@hotmail.com |
Curtis_Baumbach | Hobart91@hotmail.com |
Leola.Ward | |
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 | |
Kayley.Turcotte98 | Angelita.Altenwerth96@hotmail.com |
Curtis_Baumbach | Hobart91@hotmail.com |
Leola.Ward | |
Stan_Sauer4 | Kiera73@hotmail.com |
View on DB Fiddle
Мы рассмотрели наиболее полезные и часто используемые возможности регулярных выражений в SQL, но они ими не ограничиваются. Полный перечень возможностей вы можете посмотреть в документации.
Выводы
В этом уроке мы познакомились с оператором SIMILAR TO
. Он помогает формировать шаблоны для фильтрации текстовых полей. Повторим самые полезные и часто используемые спецсимволы:
- Символ
%
соответствует любому количеству любых символов - Квадратные скобки
[]
используются для перечисления конкретных символов — например,[АЕИОУЭЮ]
- Дефис
-
в квадратных скобках означает перечисление символов — например,[0-9]
соответствует любой цифре - Подчеркивание
_
означает ровно один любой символ (букву или цифру)
Дополнительные материалы

Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты