Самоучитель по SQL-сервер в Linux



Строковые операторы

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




Книжный магазин