строка LIKE шаблон [ESCAPE спецсимвол]
строка NOT LIKE шаблон [ESCAPE спецсимвол]

Выражение LIKE возвращает true, если строка соответствует заданному шаблону. (Как можно было ожидать, выражение NOT LIKE возвращает false, когда LIKE возвращает true, и наоборот. Этому выражению равносильно выражение NOT (строка LIKE шаблон).)

Если шаблон не содержит знаков процента и подчёркиваний, тогда шаблон представляет в точности строку и LIKE работает как оператор сравнения. Подчёркивание (_) в шаблоне подменяет (вместо него подходит) любой символ; а знак процента (%) подменяет любую (в том числе и пустую) последовательность символов.

Несколько примеров:

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

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

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

Также можно отказаться от спецсимвола, написав ESCAPE ''. При этом механизм спецпоследовательностей фактически отключается и использовать знаки процента и подчёркивания буквально в шаблоне нельзя.

Вместо LIKE можно использовать ключевое слово ILIKE, чтобы поиск был регистр-независимым с учётом текущей языковой среды. Этот оператор не описан в стандарте SQL; это расширение Postgres Pro.

Кроме того, в Postgres Pro есть оператор ~~, равнозначный LIKE, и ~~*, соответствующий ILIKE. Есть также два оператора !~~ и !~~*, представляющие NOT LIKE и NOT ILIKE, соответственно. Все эти операторы относятся к особенностям Postgres Pro. Вы можете увидеть их, например, в выводе команды EXPLAIN, так как при разборе запроса проверка LIKE и подобные заменяются ими.

Фразы LIKE, ILIKE, NOT LIKE и NOT ILIKE в синтаксисе Postgres Pro обычно обрабатываются как операторы; например, их можно использовать в конструкциях выражение оператор ANY (подвыражение), хотя предложение ESCAPE здесь добавить нельзя. В некоторых особых случаях всё же может потребоваться использовать вместо них нижележащие операторы.

строка SIMILAR TO шаблон [ESCAPE спецсимвол]
строка NOT SIMILAR TO шаблон [ESCAPE спецсимвол]

Оператор SIMILAR TO возвращает true или false в зависимости от того, соответствует ли данная строка шаблону или нет. Он работает подобно оператору LIKE, только его шаблоны соответствуют определению регулярных выражений в стандарте SQL. Регулярные выражения SQL представляют собой любопытный гибрид синтаксиса LIKE с синтаксисом обычных регулярных выражений.

Как и LIKE, условие SIMILAR TO истинно, только если шаблон соответствует всей строке; это отличается от условий с регулярными выражениями, в которых шаблон может соответствовать любой части строки. Также подобно LIKE, SIMILAR TO воспринимает символы _ и % как знаки подстановки, подменяющие любой один символ или любую подстроку, соответственно (в регулярных выражениях POSIX им аналогичны символы . и .*).

Помимо средств описания шаблонов, позаимствованных от LIKE, SIMILAR TO поддерживает следующие метасимволы, унаследованные от регулярных выражений POSIX:

Обратите внимание, точка (.) не является метасимволом для оператора SIMILAR TO.

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

Несколько примеров:

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

Функция substring с тремя параметрами, substring(строка from шаблон for спецсимвол) извлекает подстроку, соответствующую шаблону регулярного выражения SQL. Как и с SIMILAR TO, указанному шаблону должна соответствовать вся строка; в противном случае функция не найдёт ничего и вернёт NULL. Для обозначения части шаблона, которая должна быть возвращена в случае успеха, шаблон должен содержать два спецсимвола и кавычки (") после каждого. Эта функция возвращает часть шаблона между двумя такими маркерами.

Несколько примеров с маркерами #", выделяющими возвращаемую строку:

substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL

В Таблице 9.14 перечислены все существующие операторы для проверки строк регулярными выражениями POSIX.

Регулярные выражения POSIX предоставляют более мощные средства поиска по шаблонам, чем операторы LIKE и SIMILAR TO. Во многих командах Unix, таких как egrep, sed и awk используется язык шаблонов, похожий на описанный здесь.

Регулярное выражение — это последовательность символов, представляющая собой краткое определение набора строк (регулярное множество). Строка считается соответствующей регулярному выражению, если она является членом регулярного множества, описываемого регулярным выражением. Как и для LIKE, символы шаблона непосредственно соответствуют символам строки, за исключением специальных символов языка регулярных выражений. При этом спецсимволы регулярных выражений отличается от спецсимволов LIKE. В отличие от шаблонов LIKE, регулярное выражение может совпадать с любой частью строки, если только оно не привязано явно к началу и/или концу строки.

Несколько примеров:

'abc' ~ 'abc'    true
'abc' ~ '^a'     true
'abc' ~ '(b|d)'  true
'abc' ~ '^(b|c)' false

Более подробно язык шаблонов в стиле POSIX описан ниже.

Функция substring с двумя параметрами, substring(строка from шаблон), извлекает подстроку, соответствующую шаблону регулярного выражения POSIX. Она возвращает фрагмент текста, подходящий шаблону, если таковой находится в строке, либо NULL в противном случае. Но если шаблон содержит скобки, она возвращает первое подвыражение, заключённое в скобки (то, которое начинается с самой первой открывающей скобки). Если вы хотите использовать скобки, но не в таком особом режиме, можно просто заключить в них всё выражение. Если же вам нужно включить скобки в шаблон до подвыражения, которое вы хотите извлечь, это можно сделать, используя группы без захвата, которые будут описаны ниже.

Несколько примеров:

substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

Функция regexp_replace подставляет другой текст вместо подстрок, соответствующих шаблонам регулярных выражений POSIX. Она имеет синтаксис regexp_replace(исходная_строка, шаблон, замена [, флаги]). Если исходная_строка не содержит фрагмента, подходящего под шаблон, она возвращается неизменной. Если же соответствие находится, возвращается исходная_строка, в которой вместо соответствующего фрагмента подставляется замена. Строка замена может содержать \n, где n — число от 1 до 9, указывающее на исходный фрагмент, соответствующий n-ому подвыражению в скобках, и может содержать обозначение \&, указывающее, что будет вставлен фрагмент, соответствующий всему шаблону. Если же в текст замены нужно включить обратную косую черту буквально, следует написать \\. В необязательном параметре флаги передаётся текстовая строка, содержащая ноль или более однобуквенных флагов, меняющих поведение функции. Флаг i включает поиск без учёта регистра, а флаг g указывает, что заменяться должны все подходящие подстроки, а не только первая из них. Допустимые флаги (кроме g) описаны в Таблице 9.22.

Несколько примеров:

regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                   fooXarYXazY

Функция regexp_matches возвращает текстовый массив из всех подходящих подстрок, полученных в результате применения регулярного выражения POSIX. Она имеет синтаксис regexp_matches(строка, шаблон [, флаги]). Эта функция может вовсе не вернуть строк или вернуть одну или несколько строк (см. описание флага g ниже). Если шаблон не находится в строке, функция не возвращает строк. Если шаблон не содержит подвыражений в скобках, тогда для каждой строки возвращается массив с одним элементом, содержащим подстроку, соответствующую всему шаблону. Если же шаблон содержит подвыражения в скобках, функция возвращает массив элементов, в котором n-й элемент соответствует n-му подвыражению в скобках (не считая скобки «без захвата»; подробнее об этом ниже). В необязательном параметре флаги передаётся текстовая строка, содержащая ноль или более однобуквенных флагов, меняющих поведение функции. Флаг g указывает, что функция должна находить в строке не только первое, а все соответствия шаблону, и возвращать строку для каждого соответствия. Допустимые флаги (кроме g) описаны в Таблице 9.22.

Несколько примеров:

SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches 
----------------
 {bar,beque}
(1 row)

SELECT regexp_matches(
 'foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches 
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

SELECT regexp_matches('foobarbequebaz', 'barbeque');
 regexp_matches 
----------------
 {barbeque}
(1 row)

Добиться, чтобы функция regexp_matches() всегда возвращала одну строку, можно с помощью вложенного подзапроса; это особенно полезно в списке выборки SELECT, когда нужно получить все строки, в том числе и не соответствующие шаблону:

SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;

Функция regexp_split_to_table разделяет строку, используя в качестве разделителя шаблон регулярного выражения POSIX. Она имеет синтаксис regexp_split_to_table(строка, шаблон [, флаги]). Если шаблон не находится в переданной строке, возвращается вся строка целиком. Если находится минимум одно вхождение, для каждого такого вхождения возвращается текст от конца предыдущего вхождения (или начала строки) до начала вхождения. После последнего найденного вхождения возвращается фрагмент от его конца до конца строки. В необязательном параметре флаги передаётся текстовая строка, содержащая ноль или более однобуквенных флагов, меняющих поведение функции. Флаги, которые поддерживает regexp_split_to_table, описаны в Таблице 9.22.

Функция regexp_split_to_array ведёт себя подобно regexp_split_to_table, за исключением того, что regexp_split_to_array возвращает результат в массиве элементов типа text. Она имеет синтаксис regexp_split_to_array(строка, шаблон [, флаги]). Параметры у этой функции те же, что и у regexp_split_to_table.

Несколько примеров:

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps 
 over   
 the    
 lazy   
 dog    
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo 
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)

Как показывает последний пример, функции разделения по регулярным выражениям игнорируют вхождения нулевой длины, идущие в начале и в конце строки, а также непосредственно за предыдущим вхождением. Это поведение противоречит строгому определению поиска по регулярным выражениям, который реализует regexp_matches, но обычно более удобно на практике. Подобное поведение наблюдается и в других программных средах, например в Perl.