Зарегистрируйтесь, чтобы продолжить обучение

Регулярные выражения SQL

Представим, что нам нужно найти всех пользователей, чье имя начинается с букв '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 email 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 email 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 email 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 email
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 email
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] соответствует любой цифре
  • Подчеркивание _ означает ровно один любой символ (букву или цифру)

Дополнительные материалы

  1. Что такое тильда в PostgreSQL

Аватары экспертов Хекслета

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

Вам ответят команда поддержки Хекслета или другие студенты

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff