Regexps in PostgreSQL / Регулярные выражения в PostgreSQL

Опубликовано чт, 25/01/2007 - 03:00 пользователем postgresmen

Автор: Иван Золотухин

Маленькая заметка справочного характера на тему регулярных выражений в PostgreSQL

В PostgreSQL применяется 3 различных подхода к регулярным выражениям:

  • традиционный в SQL оператор LIKE
  • более новый оператор SIMILAR TO (добавлен в стандарте SQL:1999)
  • POSIX-совместимые регулярные выражения

Если предоставленных возможностей не хватает, предлагается использовать процедурные языки (например PL/Perl) и работать со строками оттуда. Также, важно отметить, что регулярные выражения не обладают чудесным быстродействием, поэтому не подходят для текстового поиска в больших объемах данных - для этой цели лучше использовать полнотекстовый поиск Tsearch2.

 

LIKE

Синтакс этого оператора в постгресе полностью традиционен: символ процента (%) соответствует любой строке в 0 или более символов, символ нижнего подчеркивания (_) матчит любой одиночный символ. Несколько примеров из документации должны полностью прояснить ситуацию:

test=# SELECT 'abc' LIKE 'abc';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' LIKE 'a%';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' LIKE '_b_';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' LIKE 'c';
 ?column? 
----------
 f
(1 row)

Следует также добавить, что паттерн в операторе LIKE матчит сразу всю строку. Поэтому, для того чтобы найти какую-нибудь последовательность внутри строки, необходимо начинать и заканчивать ваш паттерн символами %.

Для матча литералов % и _ их, как обычно, необходимо эскейпить символом обратного слеша (\).

Важное замечание! Так как символ обратного слеша (\) уже имеет особый смысл (он является escape-символом) в строковых литералах в Постгресе, его всегда тоже необходимо эскейпить опять же обратным слешем! Это приводит к конструкциям вида \\\\ для матча литерала обратного слеша в паттернах оператора LIKE. Для облегчения понимания можно представлять себе последовательную обработку регулярного выражения двумя различными парсерами: первый делает из строки настоящий регэксп, а второй - непосредственно парсит уже финальный вид регулярного выражения. Вы, однако, можете поменять escape-символ с помощью конструкции string LIKE pattern ESCAPE escape-character.

Для case-insensitive матча регулярным выражением в постгресе используется оператор ILIKE, что является одной из самых известных его gotchas.

Напоследок в этой секции скажу, что есть эквиваленты указанных операторов. Их не нужно использовать, так как они типичны только для Постгреса, но про них нужно знать, так как иногда некоторые гуру используют их в своей речи. Итак:

~~ эквивалентен LIKE

~~* эквивалентен ILIKE

!~~ эквивалентен NOT LIKE

!~~* эквивалентен NOT ILIKE

 

SIMILAR TO

Этот оператор очень сильно похож на LIKE. Отличие лишь в том, что он интерпретирует регулярные выражения по стандарту SQL, который представляет собой любопытную смесь регэкспов нотации LIKE и общеупотребительных регэкспов.

Символы % и _ имеют точно такой же смысл, что и в LIKE, однако добавлены POSIX-подобные метасимволы:

  • Символ | обозначает выбор одной либо другой альтернативы
  • Символ * обозначает повторение предыдущего элемента 0 или более раз
  • Символ + обозначает повторение предыдущего элемента 1 или более раз
  • Круглый скобки () могут быть использованы для группировки элементов в один логический атом
  • Квадратные скобки [] определяют класс символов в точности так, как это сделано в POSIX-совместимых регулярных выражениях.

Важно помнить, что метасимволы ограниченного повторения (? и {...}) в SIMILAR TO не поддерживаются, хотя они и существуют в POSIX. Также символ точки (.) не является метасимволом.

Стандартные примеры из документации:

test=# SELECT 'abc' SIMILAR TO 'abc';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' SIMILAR TO 'a';
 ?column? 
----------
 f
(1 row)

test=# SELECT 'abc' SIMILAR TO '%(b|d)%';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' SIMILAR TO '(b|c)%';
 ?column? 
----------
 f
(1 row)

Также SQL-совместимые регулярные выражения поддерживаются функцией substring(string from pattern for escape-character), которая может извлекать по паттернам подстроки, ее описание смотрите в документации. Не забывайте, правда, что вызванная с двумя, а не с тремя параметрами, она будет работать в POSIX-режиме. Также следует помнить о том, что эскейп-символ можно менять при надобности: string SIMILAR TO pattern ESCAPE escape-character

 

POSIX

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

Регэкспы в POSIX-нотации работают в PostgreSQL со следующими операторами:

~ осуществляет case-sensitive матч

~* осуществляет case-insensitive матч

!~ и !~* означают отрицание вышеуказанных операторов

Стандартные примеры:

test=# SELECT 'abc' ~ 'abc';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' ~ '^a';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' ~ '(b|d)';
 ?column? 
----------
 t
(1 row)

test=# SELECT 'abc' ~ '^(b|c)';
 ?column? 
----------
 f
(1 row)

Также, такие регэкспы могут быть использованы в функциях substring(string from pattern) и regexp_replace(source, pattern, replacement [, flags]).

Они поддерживают обычные символьные классы (как длинные названия вроде [[:digit:]], так и сокращения типа \d), группировки, метасимволы вроде точки, опережающие проверки, ограничения жадности и другие полезные операции.

Однако, не совсем привычны некоторые ограничения в регекспах:

\A соответствует началу строки

\m соответствует началу слова

\M соответствует концу слова

\y соответствует началу или концу слова

\Y соответствует только не началу и не концу слова

\Z соответствует концу строки

Однако, как уже было сказано выше, из-за специального значения символа обратного слеша в строковых литералах, его необходимо эскейпить, поэтому не забывайте писать не просто \w, если хотите сматчить любой алфавитно-цифровой символ, а \\w, чтобы регэксп работал, как и предполагается!

Например:

test=# SELECT '123' ~ '^\d{3}';
 ?column? 
----------
 f
(1 row)

test=# SELECT '123' ~ '^\\d{3}';
 ?column? 
----------
 t
(1 row)

Обратные ссылки (back references) на сгруппированные ранее подстроки доступны в функции regexp_replace как \i, где i является номером группировки.

 

Замечания

Необходимо сделать ряд важных замечаний о POSIX-регэкспах. Во-первых, PostgreSQL поддерживает несколько их "оттенков":

  • basic регулярные выражения (BREs), соответствующие редактору ed
  • extended регулярные выражения (EREs), которые приблизительно соответствуют egrep
  • advanced регулярные выражения (AREs), представляющие собой EREs с дополнительными не-POSIX возможностями, взятыми из языков Perl и Tcl.

Пользователь может выбирать как именно трактуются его POSIX-паттерны в сессии с помощью переменной regexp_flavor (по умолчанию переменная установлена в advanced):

test=# SHOW regex_flavor;
 regex_flavor 
--------------
 advanced
(1 row)

Используйте установку regexp_flavor в extended для обратной совместимости с PostgreSQL версии младше 7.4. Также стоит упомянуть о возможности менять поведение паттернов прямо run-time при помощи модификаторов регэкспов, ищите в документации или спрашивайте здесь.

 

Полезные ссылки

  1. http://www.postgresql.org/docs/8.1/static/functions-matching.html - основная статья в документации PostgreSQL про регулярные выражения. По сути эта заметка является выжимкой из этой основной статьи.
  2. http://www.oreillynet.com/pub/a/databases/2006/02/02/postgresq_regexes.html - довольно свежая статейка с множеством рецептов на ту же тему.

Any feedback is welcome at iz at sai dot msu dot ru

Jun 2006