Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

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

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 email 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 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

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 email

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 email

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] соответствует любой цифре

  • Подчеркивание _ означает ровно один любой символ (букву или цифру)


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

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

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

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

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

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

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

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

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

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

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

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

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы
профессия
Обучитесь разработке бэкенда сайтов и веб-приложений — серверной части, которая отвечает за логику и базы данных
10 месяцев
с нуля
Старт 21 ноября
профессия
Выполняйте ручное тестирование веб-приложений, находите ошибки в продукте. Узнайте все о тест-дизайне.
4 месяца
с нуля
Старт 21 ноября
профессия
Научитесь разработке веб-приложений, сайтов и программного обеспечения на языке Java, программируйте и используйте структуры данных
10 месяцев
с нуля
Старт 21 ноября
профессия
новый
Собирайте, анализируйте и интерпретируйте данные, улучшайте бизнес-процессы и продукт компании. Обучитесь работе с библиотеками Python
9 месяцев
с нуля
Старт 21 ноября
профессия
Занимайтесь созданием сайтов, веб-приложений, сервисов и их интеграцией с внутренними бизнес-системами на бекенд-языке PHP
10 месяцев
с нуля
Старт 21 ноября
профессия
Обучитесь разработке визуальной части сайта — фронтенда, а также реализации серверной — бэкенда. Освойте HTML, CSS, JavaScript
16 месяцев
с нуля
Старт 21 ноября
профессия
Разработка бэкенд-компонентов для веб-приложений
10 месяцев
с нуля
Старт 21 ноября
профессия
новый
Организовывайте процесс автоматизации тестирования на проекте, обучитесь языку программирования JavaScript, начните управлять процессом тестирования
8 месяцев
c опытом
Старт 21 ноября

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»