Теория: Регулярные выражения

Представим, что нам нужно найти всех пользователей, чье имя начинается с букв '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]%';

View on DB Fiddle

Разберем примененный шаблон [AB]%. В квадратных скобках перечисляются допустимые символы, а далее следует знакомый нам символ %. Этот запрос вернет пользователей с именами Abigale, Andy, Brayan, и так далее.

Представим, что нам нужно найти пользователей, у которых username заканчивается любой буквой. Это можно сделать таким шаблоном: %[abcdefghijklmnopqrstuvwxyz]. Согласитесь, такая запись неудобна для чтения и записи. А что, если мы случайно пропустим какую-то букву? В квадратных скобках можно использовать символ - для перечисления. Если записать в квадратных скобках начальный символ, поставить "-" и указать конечный символ, то такой шаблон вернет любой символ из диапазона от начального до конечного. Напишем запрос, который вернет всех пользователей по условию: username заканчивается любой буквой. Он будет выглядеть так:

SELECT
    id,
    username,
    email,
    birthday
FROM users
WHERE username SIMILAR TO '%[a-z]';

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]';

View on DB Fiddle

Такой запрос вернет всех пользователей, чей username заканчивается на любую цифру.

Комбинируя правила, мы можем создавать достаточно сложные шаблоны. Например, проверим, что в поле email введены корректные адреса электронной почты.

Корректная почта должна содержать адрес, который состоит из:

• Имени с любым количеством любых символов — например, my_email
• Символа @
• Домена с любым количеством любых символов — например, gmail
• Точки
• Указания национальной зоны — например, com

Запрос на поиск корректных адресов будет таким:

SELECT
    username,
    email
FROM users WHERE email SIMILAR TO '%@%.%';

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 '%.___';

View on DB Fiddle

Мы рассмотрели наиболее полезные и часто используемые возможности регулярных выражений в SQL, но они ими не ограничиваются. Полный перечень возможностей вы можете посмотреть в документации.

Выводы

В этом уроке мы познакомились с оператором SIMILAR TO. Он помогает формировать шаблоны для фильтрации текстовых полей. Повторим самые полезные и часто используемые спецсимволы:

• Символ % соответствует любому количеству любых символов
• Квадратные скобки [] используются для перечисления конкретных символов — например, [АЕИОУЭЮ]
• Дефис - в квадратных скобках означает перечисление символов — например, [0-9] соответствует любой цифре
• Подчеркивание _ означает ровно один любой символ (букву или цифру)