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

Регулярные выражения (regexp) — инструмент для поиска и обработки текстовых данных. PostgreSQL предоставляет одну из наиболее полных реализаций регулярных выражений среди всех СУБД, поддерживая синтаксис POSIX с несколькими вариациями.
В данном лонгриде сделаем первые шаги в мир регулярных выражений POSIX. Подробное описание всех функций, операторов, атомов, определителей и так далее - в Документации к PostgreSQL.

Типы регулярных выражений POSIX

PostgreSQL предоставляет механизмы работы с регулярными выражениями, основанным на POSIX-стандарте и его расширениях.
При этом важно понимать, что PostgreSQL использует свой внутренний regexp-движок, который поддерживает BRE, ERE и ARE, но SQL-интерфейс даёт доступ только к ERE/ARE, что часто вызывает путаницу.

RE (Regular Expression) - это общее название для регулярок в PostgreSQL. Когда документация говорит "RE", она обозначает любой регулярный шаблон POSIX, который движок может интерпретировать.

POSIX определяет два вида регулярных выражений:

BRE (Basic Regular Expressions) - базовые регулярные выражения
ERE (Extended Regular Expressions) - расширенные регулярные выражения

PostgreSQL дополнительно реализует:
ARE (Advanced Regular Expressions) - расширения, добавляющие синтаксис, похожий на Perl/Tcl

Хотя PostgreSQL поддерживает все три вида в своём regexp-движке, SQL-операторы и функции используют только ARE (или ERE, если расширений нет).

SQL-оператор ~, ~*, !~, !~* → всегда ARE/ERE
функции regexp_* → всегда ARE/ERE
BRE в SQL НЕ используется, даже если написать синтаксис BRE

Отличия BRE, ERE и ARE

BRE - POSIX-стиль, где многие конструкции требуют экранирования. Наиболее ограниченная и устаревшая форма.

ERE - более современная форма, похожая на egrep.
Группы и квантификаторы не экранируются, доступны конструкции: +, ?, |
Отсутствуют флаги типа (?i)

ARE - расширение PostgreSQL, включающее:

поддержка escape-последовательностей: \t, \n, \x1B
классы символов Unicode: \p{L}, \p{Digit}
флаги модификаторы: (?i) для регистронезависимого поиска
жадные и ленивые квантификаторы: *?, +?, ??
опережающие и ретроспективные проверки

Сравнение типов:

Функция	RE/BRE	ERE	ARE
Метасимволы	`^ $ . [ ] *`	`^ $ . [ ] * + ? { } \| ( )`	Все ERE + расширения
Экранирование	Часто требуется	Реже требуется	Минимальное
Квантификаторы	`*` только	`* + ? { }`	`* + ? { }` с ленивыми версиями
Группировка	``	`( )`	`( )` с расширениями
Альтернатива	Не поддерживается	`\|`	`\|`
Модификаторы	Нет	Нет	`(?i)`, `(?m)` и др.

Атомы, квантификаторы, определители, спецсимволы

Атомы (Atoms) - это минимальная неделимая единица регулярного выражения, которая может соответствовать одному символу или группе символов.

Атом	Описание	Пример
`a`	Литерал, совпадает с символом a	`SELECT 'abc' ~ 'a';`
`.`	Любой одиночный символ (кроме новой строки)	`SELECT 'abc' ~ 'a.c';` → совпадение `abc`
`[abc]`	Любой символ из множества	`SELECT 'b' ~ '[abc]';` → true
`[^abc]`	Любой символ, кроме указанных	`SELECT 'd' ~ '[^abc]';` → true
`(abc)`	Группа (используется для обратных ссылок)	`SELECT regexp_match('abc', '(a)(b)(c)');`

Квантификаторы (Quantifiers) - указывает количество повторений атома или группы, с которыми должно совпасть регулярное выражение.

Квантификатор	Значение	Пример
`*`	0 или более	`'a*'` → `''`, `'a'`, `'aa'`, ...
`+`	1 или более	`'a+'` → `'a'`, `'aa'`, ...
`?`	0 или 1	`'a?'` → `''`, `'a'`
`{n}`	ровно n	`'a{3}'` → `'aaa'`
`{n,}`	n или более	`'a{2,}'` → `'aa'`, `'aaa'`, ...
`{n,m}`	от n до m	`'a{2,4}'` → `'aa'`, `'aaa'`, `'aaaa'`

Определители (Anchors / Boundaries) - задают положение совпадения в строке, а не сами символы.

Определитель	Значение	Пример
`^`	Начало строки	`SELECT 'abc' ~ '^a';` → true
`$`	Конец строки	`SELECT 'abc' ~ 'c$';` → true
`\m` или `\<`	Начало слова	`SELECT 'cat dog' ~ '\mcat';` → true
`\M` или `\>`	Конец слова	`SELECT 'cat dog' ~ 'cat\M';` → true
`\y`	Граница слова	`SELECT 'cat dog' ~ '\ycat\y';` → true
`\Y`	Не-граница слова	редко используется

Спецсимволы (Special Characters / Metacharacters) - это символы, которые не трактуются буквально, а задают особое поведение в регулярном выражении.

Символ	Значение	Пример
`.`	любой одиночный символ	`'a.c'` → совпадает с `abc`
`\|`	альтернация ("или")	`'a\|b'` → `'a'` или `'b'`
`()`	группировка	`(ab)+` → `'ab'`, `'abab'`, ...
`(?:)`	негруппирующая группа (ARE)	`(?:ab)+`
`\d`	цифра (ARE)	`'a\d'` → `'a1'`, `'a5'`, ...
`\w`	буква/цифра/подчёркивание (ARE)	`'user\w'` → `'user1'`, `'user_'`
`\s`	пробельный символ (ARE)	`'a\s+'` → `'a '`, `'a\t'`, `'a\n'`
`\`	экранирует спецсимвол	`'\.'` → точка как символ

Операторы регулярных выражений

~ - соответствует регулярному выражению

sql

select 'PostgreSQL' ~ 'post';    -- false
select 'PostgreSQL' ~ 'Post';    -- true

!~ - не соответствует регулярному выражению

sql

select 'PostgreSQL' !~ 'mysql';  -- true
select 'test@example.com' !~ '^[a-z]+@[a-z]+\.[a-z]{2,}$';  -- false

~* - соответствует, регистр игнорируется

sql

select 'PostgreSQL' ~* 'post';   -- true
select 'Анна' ~* 'анна';         -- true

!~* - не соответствует, регистр игнорируется

sql

select 'PostgreSQL' !~* 'mysql'; -- true
select 'PostgreSQL' !~* 'post';  -- false

Функции регулярных выражений

regexp_like(string, pattern [, flags]) - проверяет соответствие (аналог ~). Появилась в PostgreSQL 15 версии.

sql

-- Базовое использование
select regexp_like('PostgreSQL', 'Post.*'); -- true

-- С флагами
select regexp_like('PostgreSQL', 'post.*', 'i'); -- true (флаг 'i' - ignore case)

-- Проверка email
select regexp_like('test@example.com', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'); -- true

regexp_match(string, pattern [, flags]) - возвращает первое совпадение в виде массива.

sql

-- Простое извлечение
select regexp_match('PostgreSQL 15.2', '\d+\.\d+'); -- {"15.2"}

-- Извлечение нескольких групп
select regexp_match('John Doe, 30 years', '(\w+)\s+(\w+),\s+(\d+)'); -- {"John","Doe","30"}

-- Извлечение даты
select regexp_match('2023-12-15', '(\d{4})-(\d{2})-(\d{2})'); -- {"2023","12","15"}

regexp_matches(string, pattern [, flags]) - возвращает все совпадения в виде множества массивов.

sql

-- Все совпадения
select regexp_matches('test1 test2 test3', '\w+\d', 'g');
-- {"test1"}
-- {"test2"}
-- {"test3"}

-- Извлечение всех чисел из текста
select regexp_matches('Price: $100, $200, $300', '\$\d+', 'g');
-- {"$100"}
-- {"$200"}
-- {"$300"}

-- С группами захвата
select regexp_matches('name: John, age: 30', '(\w+):\s*(\w+)', 'g');
-- {"name","John"}
-- {"age","30"}

substring(string from pattern) - извлекает подстроку по регулярному выражению.

sql

-- Извлечение домена из email
select substring('user@example.com' from '@([^@]+)$'); -- "example.com"

-- Извлечение числа из строки
select substring('Order #12345 confirmed' from '#(\d+)'); -- "12345"

-- Извлечение текста в кавычках
select substring('Say "hello" to world' from '"([^"]+)"'); -- "hello"

regexp_count(string, pattern [, flags]) - считает количество совпадений. Появилась в PostgreSQL 15 версии.

sql

--Найти количество вхождений post без учета регистра
select regexp_count('PostgreSQLсамыйPostgreSQLизPostgreSQL', 'post', 1, 'i'); --3

--Получить количество цифр
select regexp_count('a1b22c333', '[0-9]'); --6

--Подсчитать количество повторяющихся слов
select regexp_count('cat dog cat mouse cat', '\mcat\M'); --3

regexp_replace(string, pattern, replacement [, flags]) - замена первого или всех (с флагом g) совпадений.

sql

-- Простая замена
select regexp_replace('Hello World', 'World', 'PostgreSQL'); -- "Hello PostgreSQL"

-- Маскирование данных
select regexp_replace('Credit card: 1234-5678-9012-3456', 
    '\d{4}-\d{4}-\d{4}', 'XXXX-XXXX-XXXX'); -- "Credit card: XXXX-XXXX-XXXX-3456"

-- Форматирование телефона
select regexp_replace('+71234567890', 
    '\+7(\d{3})(\d{3})(\d{2})(\d{2})', '+7 (\1) \2-\3-\4'); -- "+7 (123) 456-78-90"

-- Удаление лишних пробелов
select regexp_replace('Hello     World    !', '\s+', ' ', 'g'); -- "Hello World !"

regexp_split_to_table(string, pattern) - разбивает строку на строки.

sql

-- Разбиение CSV строки
select regexp_split_to_table('apple,banana,cherry', ',');
-- apple
-- banana
-- cherry

-- Разбиение по нескольким разделителям
select regexp_split_to_table('Hello;World.Test|Example', '[;.|]');
-- Hello
-- World
-- Test
-- Example

-- Разбиение текста на слова
select regexp_split_to_table('The quick brown fox', '\s+');
-- The
-- quick
-- brown
-- fox

regexp_split_to_array(string, pattern) - разбивает строку на массив.

sql

-- Разбиение в массив
select regexp_split_to_array('apple,banana,cherry', ','); -- {apple,banana,cherry}

-- Разбиение пути файла
select regexp_split_to_array('/usr/local/bin', '/'); -- {"",usr,local,bin}

-- Разбиение даты
select regexp_split_to_array('2023-12-15', '-'); -- {2023,12,15}

regexp_substr(string, pattern [, flags]) - извлекает подстроку по номеру вхождения. Появилась в PostgreSQL 15 версии.

sql

-- Первое вхождение
select regexp_substr('abc123def456', '\d+'); -- "123"

-- Второе вхождение
select regexp_substr('abc123def456', '\d+', 1, 2); -- "456"

-- Извлечение домена из URL
select regexp_substr('https://www.example.com/path', 'https?://([^/]+)', 1, 1, '', 1); -- "www.example.com"

Lookahead / Lookbehind

Опережающая проверка (Lookahead) - проверяет, что за текущей позицией в строке есть определённый шаблон, не включая его в результат.

Позитивный lookahead: (?=...) — проверяет, что шаблон присутствует
Негативный lookahead: (?!...) — проверяет, что шаблон не присутствует

sql

--Позитивный lookahead
select regexp_matches('abc1 abc2 abc3', 'abc(?=\d)', 'g');

--Шаблон abc(?=\d) → ищем abc, за которым сразу идёт цифра
--Результат: 'abc', 'abc', 'abc' (три раза)

--Негативный lookahead
select regexp_matches('abc abc1 abc2', 'abc(?!\d)', 'g');

--Шаблон abc(?!\d) → ищем abc, за которым не идёт цифра
--Результат: 'abc' (только первый abc)

Ретроспективная проверка (Lookbehind) - проверяет, что перед текущей позицией в строке есть определённый шаблон, не включая его в результат.

Позитивный lookbehind: (?<=...) — проверяет, что шаблон присутствует перед текущей позицией
Негативный lookbehind: (?<!...) — проверяет, что шаблон не присутствует перед текущей позицией

sql

--Позитивный lookbehind
select regexp_matches('abc1 abc2 abc3', '(?<=abc)\d', 'g');

--Шаблон (?<=abc)\d → ищем цифру, перед которой есть abc
--Результат: '1', '2', '3'

--Негативный lookbehind
select regexp_matches('x1 y1 z1', '(?<!x)\d', 'g');

--Шаблон (?<!x)\d → ищем цифру, перед которой нет x
--Результат: '1', '1' (цифры после y и z)

Заключение

Регулярные выражения — это не только инструмент, но и язык программирования для работы с текстом. Освоив его, вы сможете решать сложные задачи обработки данных непосредственно на уровне СУБД, уменьшая нагрузку на прикладной код и повышая общую эффективность системы.

Несколько выводов:

ARE — наиболее мощный и рекомендуемый к использованию тип регулярных выражений
операторы ~, ~*, !~, !~* идеальны для простых проверок
функции regexp_match(), regexp_replace(), regexp_split_to_array() покрывают большинство потребностей
для сложной обработки текста используйте комбинацию различных функций

И не забывайте учитывать производительность при работе с большими объемами данных!

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

Типы регулярных выражений POSIX ​

Отличия BRE, ERE и ARE ​

Атомы, квантификаторы, определители, спецсимволы ​

Операторы регулярных выражений ​

Функции регулярных выражений ​

Lookahead / Lookbehind ​

Заключение ​