Строковые операторы
В PostgreSQL существует обширный набор строковых операторов для выполнения различных действий, от простой конкатенации текста и сравнения строк до поиска по регулярным выражениям. Строковые операторы работают с типами char, varchar, а также с собственным типом PostgreSQL text.
Ниже описаны основные операторы сравнения и конкатенации, а также реализация операторов регулярных выражений с учетом и без учета регистра символов.
Базовые операторы сравнения
Базовые операторы сравнения строк и конкатенации, поддерживаемые в PostgreSQL, перечислены в табл. 5.1.
ПРИМЕЧАНИЕ
Ключевые слова LIKE и ILIKE, приводящие к вызову функции 11ke(), иногда называются операторами сравнения строк. Эти ключевые слова рассматриваются ниже в разделе «Функции».
Таблица 5.1. Базовые строковые операторы
| Оператор | Синтаксис | Описание |
| = | ‘строка!’ = ‘ строка2’ | Возвращает true, если первая строка точно совпадает со второй |
| ! = | ‘строка!’ != ‘ строка2’ | Возвращает true, если первая строка не совпадает со второй |
| о | ‘строка!’ <> ‘ строка2’ | Идентичен оператору != |
| < | ‘строка!’ < ‘ строка2’ | Возвращает true, если при лексикографической сортировке первая строка предшествует второй |
| <= | ‘строка!’ <= ‘ строка2’ | Возвращает true, если при лексикографической сортировке первая строка предшествует второй или их значения совпадают |
| > | ‘строка!’ > ‘ строка2’ | Возвращает true, если при лексикографической сортировке вторая строка предшествует первой |
| >= | ‘строка!’ >= ‘ строка2’ | Возвращает true, если при лексикографической сортировке вторая строка предшествует первой или их значения совпадают |
Все операторы сравнения строк возвращают логическое значение (true или false). Лексикографическая сортировка, упоминаемая в табл. 5.1, последовательно сравнивает символы строк и определяет, какой из символов «больше» другого. Если начальные символы двух строк совпадают, проверяются следующие символы (слева направо). Перебор продолжается до тех пор, пока не будут найдены два различающихся символа. В этом алгоритме сортировки «больший» символ выбирается сравнением ASCII-кодов, как показывает следующий пример:
booktown=# SELECT letter, ascii(letter)
booktown-# FROM text_sorting
booktown-# ORDER BY letter ASC;
letter ascii
0 48
1 49
2 50
3 51
A 65
В 66
С 67
D 68
a 97
b 98
с 99
d I 100
(12 rows)
Если вы не уверены в порядке сортировки того или иного символа, воспользуйтесь функцией ascii О для получения его ASCII-кода (функция asci i () описана ниже в разделе «Функции»). В листинге 5.3 из таблицы books выбираются названия всех книг, первая буква которых при сортировке предшествует символу «D».
Листинг 5.3. Сравнение строк
booktown=# SELECT title FROM books
booktown=# WHERE substrCtitle, 1, 1) < ‘D1;
title
2001: A Space Odyssey
Bartholomew and the Oobleck
(2 rows)
Конкатенация
Оператор конкатенации (11) играет очень важную роль при форматировании выходных данных. Как и все операторы, он может использоваться в командах SQL всюду, где могут использоваться константы. Допускается последовательная конкатенация строковых значений, для этого перечисляемые строковые константы или идентификаторы разделяются операторами 11.
Например, оператор конкатенации может использоваться в секции WHERE для фильтрации записей на основании сравнения с символьной строкой. Пример приведен в листинге 5.4.
Листинг 5.4. Конкатенация строк
booktown=# SELECT ‘The Title: ‘ | title || ‘, by ‘ |
booktown-# first_name || ‘ ‘ II lastjiame AS bookjinfo
booktown-# FROM books NATURAL JOIN authors AS a (authorjd) LIMIT 3;
book_info
The Title: The Shining, by Stephen King
The Title: Dune, by Frank Herbert
The Title: 2001: A Space Odyssey, by Arthur C. Clarke
(3 rows)
Операторы регулярных выражений
Если обычного сравнения строк оказывается недостаточно, в PostgreSQL предусмотрены специальные операторы для поиска совпадений по регулярным выражениям. Регулярное выражение похоже на строковую константу, с которой сравнивается исходная строка, но некоторые символы (квадратные скобки, вертикальная черта, обратная косая черта и т. д.) при сравнении интерпретируются особым образом. Если вам доводилось работать с такими программами Unix, как sed, grep или perl, возможно, вы уже знакомы с синтаксисом регулярных выражений.
ПРИМЕЧАНИЕ
Общие сведения о регулярных выражениях приведены в книге Джеффри Фридла (Jeffrey Friedl) «Mastering Regular Expressions».
Регулярное выражение состоит из литералов и метасимволов (специальных символов). Обычно регулярные выражения могут совпадать с несколькими вариантами строковых значений. Пример приведен в листинге 5.5, где в таблице authors ищутся все имена (fi rstjiame), начинающиеся с символа А или Т.
Листинг 5.5. Поиск по регулярному выражению
booktown=# SELECT first_name, last_name
booktown-# FROM authors
booktown-# WHERE firstjiame — «AT1;
fi rstjiame lastjiame
Ariel Denham Tom Christiansen
Arthur C. | Clarke Andrew Brookins
Theodor Seuss j Geisel (5 rows)
Символ ~ в секции WHERE является оператором регулярного выражения, а само регулярное выражение задается строкой ЛА| ЛТ. В него входят специальные символы (метасимволы) л и |, а также литералы А и Т. Метасимволы регулярных выражений описаны ниже в этом пункте.
Важнейшее различие между функцией 11 ke() и операторами регулярных выражений заключается в том, что функция likeO ищет совпадение для всей строки, а операторы регулярных выражений ищут совпадение в произвольной позиции заданной строки, если только в самом регулярном выражении не указано обратное.
Операторы регулярных выражений перечислены в табл. 5.2. Их операндами всегда являются строка с регулярным выражением и текст, в котором ищется совпадение (идентификатор или константа). Все операторы регулярных выражении
возвращают результат логического типа, интерпретация которого зависит от специфики самого оператора.
Таблица 5.2. Операторы регулярных выражений
| Оператор | Синтаксис | Описание |
| — | ‘ строка’ ~ ‘ выражение’ | Возвращает true, если в строке существует совпадение для регулярного выражения |
| i ~ | ‘строка’ \- ‘выражение’ | Возвращает true, если в строке нет совпадения для регулярного выражения |
| ~* | ‘ строка’ ~* ‘ выражение’ | Возвращает true, если в строке существует совпадение для регулярного выражения (без учета регистра символов) |
| |_* | ‘строка’ !-* ‘выражение’ | Возвращает true, если в строке нет совпадения для регулярного выражения (без учета регистра символов) |
В табл. 5.3 перечислены метасимволы регулярных выражений. Как было указано выше, в регулярных выражениях эти символы интерпретируются особым образом.
Таблица 5.3. Метасимволы регулярных выражений
| Символ | Синтаксис | Описание |
| — | ^выражение | Соответствует началу строки |
| $ | выражение$ | Соответствует концу строки |
| . | , | Соответствует одному символу |
| [ ] | [abc] | Соответствует любому из символов, перечисленных в квадратных скобках |
| Г] | ГаЬс] | Соответствует любому символу кроме символов, перечисленных в квадратных скобках |
| [-] | [a-z] | Соответствует любому символу из интервала, заданного в квадратных скобках |
| Г-] | Га-z] | Соответствует любому символу кроме символов из интервала, заданного в квадратных скобках |
| 7 | а? | Соответствует нулю экземпляров или одному экземпляру предшествующего символа или подвыражения |
| * | а* | Соответствует нулю экземпляров или произвольному количеству экземпляров предшествующего символа или подвыражения |
| + | а+ | Соответствует одному и более экземпляров предшествующего символа или подвыражения |
| 1 | выражение! \ выражение2 | Соответствует левому или правому подвыражению |
| ( ) | (выражение!) выражение2 | Группировка подвыражений с явным определением приоритета выполняемых операций |
ПРИМЕЧАНИЕ
Литералы, совпадающие с метасимволами из табл. 5.3, должны экранироваться в строке двумя символами \ (например, знак $ представляется последовательностью \\$).
Регулярные выражения часто применяются для поиска подстрок в строках-литералах большего размера. При поиске соответствия с учетом регистра символов используется оператор -; с оператором -* регистр игнорируется. Примеры приведены в листинге 5.6.
Листинг 5.6. Простой поиск соответствия
booktown=# SELECT title FROM books
booktown-# WHERE title — ‘The’;
title
The Shining The Cat in the Hat The Velveteen Rabbit
The Tell-Tale Heart
(4 rows)
booktown=# SELECT title FROM books
booktown-f WHERE title -* ‘The’; title
The Shining The Cat in the Hat Bartholomew and
the Oobleck Franklin in the Dark The Velveteen Rabbit
The Tell-Tale Heart
(6 rows)
Как видно из результатов, оператор ~* возвращает две дополнительные записи, поскольку выражение соответствует не только подстроке «the», но и любым модификациям в регистре символов этой подстроки (the, tHe, ThE и т. д.).
Если добавить в начало этого регулярного выражения символ л, оно будет соответствовать только подстроке «The», находящейся в начале исходного текста (листинг 5.7).
Конструкция . * обозначает любое количество произвольных символов до следующего подвыражения. В данном примере этим подвыражением является пара строк в круглых скобках (rabbit и heart), разделенных символом |, что соответствует любой из перечисленных строк.
Листинг 5.7. Нетривиальный поиск соответствия
booktown=f SELECT title FROM books
booktown-# WHERE title -* IAThe.*(rabbit|heart)’;
title
The Velveteen Rabbit The Tell-Tale Heart
(2 rows)
Выражение AThe.*(rabbit|heart) означает следующее: соответствие находится лишь в том случае, если строка начинается с символов «The», далее идет любое количество произвольных символов, а после них следует либо подстрока «rabbit», либо подстрока «heart». Оператор ~* (вместо оператора ~) производит сравнение без учета регистра символов.
В листинге 5.8 приведен пример еще более сложного регулярного выражения.
Листинг 5.8. Сложный поиск соответствия
booktown=# SELECT title FROM books
booktown-# WHERE title ~* ‘rt.*[ri]t) | (ingJjune$)’ :
title
The Shining Dune
The Velveteen Rabbit The Tell-Tale Heart
(4 rows)
booktown=#
На первый взгляд регулярное выражение в листинге 5.8 выглядит устрашающе. Но если разбить его на элементы, становится видно, что оно состоит из двух выражений в круглых скобках, разделенных символом [.Таким образом, соответствие будет найдено, если хотя бы одно из этих выражений указывает на значение в поле title.
Продолжая анализ выражения, мы видим, что подвыражение слева от символа | состоит из следующих элементов (слева направо): метасимвол л, за которым следует символ t, затем последовательность .* и пара квадратных скобок с символами г и i, за которыми следует символ t. В переводе на русский язык это означает, что соответствие должно начинаться с буквы t, за которой следует ноль или более символов, пока не будет встречена буква г или 1, после которой немедленно следует буква t. Если хотя бы одно из этих условий не соблюдается, соответствия нет.
Выражение в правой части устроено проще. Оно состоит из двух литералов ing и une, разделенных очередным символом , причем за каждым литералом следует символ $. В переводе это означает, что совпадение должно заканчиваться подстрокой Ing или une. При выполнении любого (благодаря символу |) из этих условий соответствие считается найденным.