Строковые операторы В 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. При выполнении любого (благодаря символу |) из этих условий соответствие считается найденным.