Регулярные выражения — Основы 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]%';

first_name

last_name

birthday

Abigale

Turner

Baby_Wintheiser@hotmail.com

2021-12-06T00:00:00.000Z

Alvera

Bergnaum

Kendall_Aufderhar@gmail.com

2021-10-11T00:00:00.000Z

Alfreda

Hermann

Tyree89@yahoo.com

2021-12-12T00:00:00.000Z

Abe

Funk

Dewayne_Lueilwitz69@hotmail.com

2021-06-16T00:00:00.000Z

Andy

Huel

Vincenza_Mertz@gmail.com

2021-07-09T00:00:00.000Z

August

Konopelski

Karianne_Treutel@gmail.com

2021-07-04T00:00:00.000Z

Alfredo

Sipes

Hiram.Schowalter24@hotmail.com

2022-01-17T00:00:00.000Z

Alejandrin

Nicolas

Elizabeth58@yahoo.com

2021-09-15T00:00:00.000Z

Alvena

Rutherford

Lina59@hotmail.com

2022-01-22T00:00:00.000Z

Brayan

Senger

Brianne.OKon75@gmail.com

2021-06-29T00:00:00.000Z

Arne

Hudson

Evie_Franey72@gmail.com

2021-08-17T00:00:00.000Z

Angelica

Donnelly

Jaylin69@hotmail.com

2021-07-31T00:00:00.000Z

Araceli

Wisozk

Ronny.Braun70@hotmail.com

2021-09-14T00:00:00.000Z

Anderson

Nader

Darrion_Yost@hotmail.com

2022-04-30T00:00:00.000Z

Andres

Heidenreich

Myrna2@gmail.com

2021-07-11T00:00:00.000Z

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, first_name, last_name, email, birthday FROM users WHERE username SIMILAR TO '%[a-z]';

first_name

last_name

birthday

Caleigh

Connelly

Hobart91@hotmail.com

2021-07-25T00:00:00.000Z

Whitney

Jenkins

Kenya_Legros17@yahoo.com

2022-02-07T00:00:00.000Z

Abigale

Turner

Baby_Wintheiser@hotmail.com

2021-12-06T00:00:00.000Z

Kelli

Zemlak

Dorian31@gmail.com

2021-06-15T00:00:00.000Z

Alfreda

Hermann

Tyree89@yahoo.com

2021-12-12T00:00:00.000Z

Nicolas

Kreiger

Audrey.Gibson@yahoo.com

2022-02-27T00:00:00.000Z

Dangelo

Jacobi

Melany_Pfeffer87@hotmail.com

2021-09-17T00:00:00.000Z

Andy

Huel

Vincenza_Mertz@gmail.com

2021-07-09T00:00:00.000Z

Jarrod

Schumm

Jasen_DAmore94@gmail.com

2021-10-09T00:00:00.000Z

Hershel

Schuster

Jaleel.Littel@gmail.com

2022-02-26T00:00:00.000Z

Kian

Heathcote

Josie.Mante@yahoo.com

2021-09-28T00:00:00.000Z

Alvena

Rutherford

Lina59@hotmail.com

2022-01-22T00:00:00.000Z

Noel

Wehner

Hailie.McLaughlin64@gmail.com

2021-08-11T00:00:00.000Z

Marco

Mertz

Crawford64@yahoo.com

2021-09-29T00:00:00.000Z

Sammie

Quitzon

Monserrat.Carter64@hotmail.com

2022-04-20T00:00:00.000Z

Samir

Okuneva

Celine.Hand70@yahoo.com

2022-02-08T00:00:00.000Z

Helena

Herzog

Electa60@gmail.com

2021-09-15T00:00:00.000Z

Mara

Marquardt

Joshua.Lesch24@yahoo.com

2022-04-14T00:00:00.000Z

Arne

Hudson

Evie_Franey72@gmail.com

2021-08-17T00:00:00.000Z

Otis

Bernier

Vladimir.Thiel2@yahoo.com

2022-02-12T00:00:00.000Z

Garret

Rempel

Dion_Jenkins@gmail.com

2021-10-29T00:00:00.000Z

Jack

Kilback

Jaunita_Gislason81@yahoo.com

2022-05-09T00:00:00.000Z

Felipe

Ruecker

Sibyl65@yahoo.com

2021-07-16T00:00:00.000Z

Virgie

Hayes

Fernando.Lakin@hotmail.com

2022-03-27T00:00:00.000Z

Jasper

Hilll

Zoey_Ziemann@hotmail.com

2021-08-22T00:00:00.000Z

Minerva

Keeling

Chance_Moen@gmail.com

2022-04-01T00:00:00.000Z

Shaun

Nitzsche

Celestino81@hotmail.com

2021-06-25T00:00:00.000Z

Luis

Balistreri

Ashlynn.Lind1@yahoo.com

2021-09-30T00:00:00.000Z

Jannie

Schuster

Tommie20@gmail.com

2021-08-07T00:00:00.000Z

Cassidy

Wyman

Rupert33@hotmail.com

2022-03-25T00:00:00.000Z

Nedra

Cummings

Salvador_Hessel83@yahoo.com

2022-02-06T00:00:00.000Z

Julianne

Hermiston

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, first_name, last_name, email, birthday FROM users WHERE username SIMILAR TO '%[0-9]';

first_name

last_name

birthday

Lucienne

Feil

Trevion53@yahoo.com

2022-05-25T00:00:00.000Z

Ramiro

Wolf

Baylee52@yahoo.com

2022-01-13T00:00:00.000Z

Maureen

Romaguera

Casimer_Cronin@yahoo.com

2022-04-21T00:00:00.000Z

Jennyfer

Flatley

Angelita.Altenwerth96@hotmail.com

2021-10-31T00:00:00.000Z

Prudence

McGlynn

Kiera73@hotmail.com

2022-04-13T00:00:00.000Z

Phyllis

Kassulke

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] соответствует любой цифре
Подчеркивание _ означает ровно один любой символ (букву или цифру)

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

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

Об обучении на Хекслете

Статья «Как учиться и справляться с негативными мыслями»
Статья «Ловушки обучения»
Статья «Сложные простые задачи по программированию»
Вебинар «Как самостоятельно учиться»

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ

130

курсов

1000

упражнений

2000+

часов теории

3200

тестов