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

Сегодня поразбираемся с тем, как в postgresql работать с регулярными выражениями.

Исходные данные: таблица users следующего вида:

id | first_name | full_name | last_name | email ------------------------------------------------------------------- 1 | Иван | Иван Дмитриев | - | ivan@mail.ru 2 | Ольга | Ольга Корнева | - | ko@ya.ru 3 | Вячеслав | Вячеслав Домашнев | - | domv@yandex.ru 4 | Игорь | Игорь Черников | - | ichern@gmail.com 5 | Галина | Галина Горных | - | Galina@Yandex.ru

Начнем с простейшего - выберем все записи, где поле email содержит подстроку ya.

Это можно сделать несколькими способами. Самый известный - использовать оператор LIKE. Запрос будет выглядеть так:
SELECT email FROM users WHERE email LIKE '%ya%'

% означает любое количество символов. _ означает один символ.

Результат выполнения:
id | first_name | full_name | last_name | email ----+------------+-------------------+-----------+---------------- 2 | Ольга | Ольга Корнева | | ko@ya.ru 3 | Вячеслав | Вячеслав Домашнев | | domv@yandex.ru

Второй вариант: используя оператор ~ (тильда) текст запроса можно сократить до:
SELECT email FROM users WHERE email ~ 'ya'

Результат выполнения аналогичен предыдущему.

Заметим, что при использовании ~ не требуется использовать % или _, что сокращает текст запроса. Однако, ~ может не поддерживаться по умолчанию в используемом фреймворке или библиотеке, например в Doctrine.

Далее: если присмотреться, то эти запросы возвращают не все подходящие под условие результаты. А именно не возвращается адрес Galina@Yandex.ru.

Так происходит потому, что поиск выполнялся с учетом регистра. Для регистронезависимого поиска запросы выглядят так:
SELECT email FROM users WHERE email ILIKE '%ya%' SELECT email FROM users WHERE email ~* 'ya'
Результат выполнения:
id | first_name | full_name | last_name | email ----+------------+-------------------+-----------+------------------ 2 | Ольга | Ольга Корнева | | ko@ya.ru 3 | Вячеслав | Вячеслав Домашнев | | domv@yandex.ru 5 | Галина | Галина Горных | | Galina@Yandex.Ru

Если регулярное выражение требуется усложнить и модификаторов %/_ недостаточно, то используем уже известную ~. Например, найдем все адреса с доменом mail или gmail:
SELECT email FROM users WHERE email ~ '@(gmail|mail)' -- или SELECT email FROM users WHERE email ~ '@(g?)mail'
Для обоих запросов результат выполнения:
email ------------------ ivan@mail.ru ichern@gmail.com

Теперь попробуем в деле функции, которые возвращают подстроки, соответствующие регулярному выражению. Для начала найдем все домены из значений поля email:
SELECT SUBSTRING(email FROM '.*@(.*)') AS domain FROM users
Результат выполнения:
domain ----------- mail.ru ya.ru yandex.ru gmail.com Yandex.Ru

Комбинируя SUBSTRING с другими функциями, можно найти, например, все различные домены первого уровня в почтовых адресах:
SELECT DISTINCT(LOWER(SUBSTRING(email FROM '.*\.(.*)$'))) AS tld FROM users;
Результат выполнения:
tld ---- ru com

И последним заданием попробуем заполнить пока еще пустое поле last_name значениями, взятыми из поля full_name. Считаем, что фамилия - это все символы после пробела в поле full_name:
UPDATE users SET last_name = SUBSTRING(full_name FROM '.*\s(.*)$') -- затем SELECT first_name, last_name FROM users
Результат выполнения:
first_name | last_name ------------+----------- Иван | Дмитриев Ольга | Корнева Вячеслав | Домашнев Игорь | Черников Галина | Горных

Резюмируя:

• простейший поиск осуществляем с помощью LIKE или ~ и их регистронезависимых аналогов ILIKE или ~*;
• поиск посложнее осуществляем с помощью ~/~*;
• для получения подстрок, соответствующих регулярному выражению, используем функцию substring()

Больше информации об использовании регулярных выражений - в официальном руководстве.