В этом разделе описаны функции и операторы для работы с текстовыми строками. Под строками в данном контексте подразумеваются значения типов character
, character varying
и text
. Если не отмечено обратное, эти функции и операторы принимают и возвращают тип text
. С тем же успехом в аргументах может передаваться тип character varying
. Аргументы же типа character
до вызова оператора или функции приводятся к типу text
, вследствие чего завершающие пробелы в значении character
будут обрезаться.
В SQL определены несколько строковых функций, в которых аргументы разделяются не запятыми, а ключевыми словами. Они перечислены в Таблице 9.9. PostgreSQL также предоставляет варианты этих функций с синтаксисом, обычным для функций (см. Таблицу 9.10).
Оператор конкатенации строк (||
) сможет принять нестроковый аргумент, если хотя бы один аргумент имеет строковый тип, как показано в Таблице 9.9. В других случаях можно использовать явное преобразование в text
, чтобы оператор принял нестроковый аргумент.
Таблица 9.9. Строковые функции и операторы языка SQL
Функция/оператор Описание Пример(ы) |
---|
Соединяет две строки.
|
Преобразует нестроковый аргумент в текст, а затем соединяет две строки. (Нестроковый аргумент не должен быть массивом, иначе возникает неоднозначность с операторами массивов
|
Проверяет, соответствует ли строка определённой форме нормализации Юникода. Форма указывается в необязательном ключевом слове
|
Возвращает число бит в строке (это число в 8 раз больше
|
Возвращает число символов в строке.
|
Переводит символы строки в нижний регистр в соответствии с правилами локали базы данных.
|
Переводит строку в заданную форму нормализации Unicode. Форма указывается в необязательном ключевом слове
|
Возвращает число байт в строке.
|
Возвращает число байт в строке. Так как эта вариация функции принимает непосредственно тип
|
Заменяет подстроку в
|
Возвращает начальную позицию первого вхождения
|
Извлекает из
|
Извлекает первую подстроку, соответствующую регулярному выражению в стиле POSIX; см. Подраздел 9.7.3.
|
Извлекает первую подстроку, соответствующую регулярному выражению в стиле SQL; см. Подраздел 9.7.2. Первая форма определена в стандарте, начиная с SQL:2003, а вторая существовала только в стандарте SQL:1999 и её следует считать устаревшей.
|
Удаляет наибольшую подстроку, содержащую только символы
|
Это нестандартный синтаксис вызова
|
Переводит символы строки в верхний регистр, в соответствии с правилами локали базы данных.
|
Кроме этого, в PostgreSQL есть и другие функции для работы со строками, перечисленные в Таблице 9.10. (Некоторые из них используются в качестве внутренней реализации стандартных строковых функций SQL, приведённых в Таблице 9.9.) Также имеются операторы сопоставления шаблонов, описанные в Разделе 9.7, и операторы полнотекстового поиска, описанные в Главе 12.
Таблица 9.10. Другие строковые функции и операторы
Функция/оператор Описание Пример(ы) |
---|
Возвращает true, если первая строка начинается со второй строки (равнозначно функции
|
Возвращает числовой код первого символа аргумента. Для UTF8 возвращает код символа в Unicode. Для других многобайтных кодировок аргумент должен быть ASCII-символом.
|
Удаляет наибольшую подстроку, содержащую только символы
|
Возвращает символ с данным кодом. Для UTF8 аргумент воспринимается как код символа Unicode, а для других кодировок он должен указывать на ASCII-символ. Эта функция не может выдать
|
Соединяет текстовые представления всех аргументов, игнорируя NULL.
|
Соединяет вместе все аргументы, кроме первого, через разделитель. Разделитель задаётся в первом аргументе и должен быть отличен от NULL. В других аргументах значение NULL игнорируется.
|
Форматирует аргументы в соответствии со строкой формата; см. Подраздел 9.4.1. Эта функция работает подобно
|
Переводит первую букву каждого слова в строке в верхний регистр, а остальные — в нижний. Словами считаются последовательности алфавитно-цифровых символов, разделённые любыми другими символами.
|
Возвращает первые
|
Возвращает число символов в строке.
|
Дополняет строку
|
Удаляет наибольшую подстроку, содержащую только символы
|
Вычисляет MD5-хеш аргумента и выдаёт результат в шестнадцатеричном виде.
|
Раскладывает полный идентификатор, задаваемый параметром
|
Возвращает имя текущей клиентской кодировки.
|
Преобразует аргумент в строку, подходящую для использования в качестве идентификатора в SQL-операторе. При необходимости идентификатор заключается в кавычки (например, если он содержит символы, недопустимые в открытом виде, или буквы в разных регистрах). Если переданная строка содержит кавычки, они дублируются. См. также Пример 43.1.
|
Преобразует аргумент в строку, подходящую для использования в качестве текстовой константы в SQL-операторе. Внутренние символы апостроф и обратная косая черта при этом дублируются. Заметьте, что
|
Переводит данное значение в текстовый вид и заключает в апострофы как текстовую строку. Символы апостроф и обратная косая черта при этом дублируются.
|
Преобразует аргумент в строку, подходящую для использования в качестве текстовой константы в SQL-операторе; при этом для аргумента NULL возвращается строка
|
Переводит данное значение в текстовый вид и заключает в апострофы как текстовую строку, при этом для аргумента NULL возвращается строка
|
Возвращает количество вхождений регулярного выражения POSIX
|
Возвращает позицию в строке
|
Проверяет, есть ли в строке
|
Возвращает подстроки из первого вхождения регулярного выражения POSIX
|
Возвращает подстроки из первого вхождения регулярного выражения POSIX
{bar} {baz}
|
Заменяет подстроку первого вхождения регулярного выражения POSIX
|
Заменяет подстроку
|
Разделяет содержимое
|
Разделяет содержимое
hello world
|
Возвращает подстроку
|
Повторяет содержимое
|
Заменяет все вхождения в
|
Переставляет символы в строке в обратном порядке.
|
Возвращает последние
|
Дополняет строку
|
Удаляет наибольшую подстроку, содержащую только символы
|
Разделяет строку
|
Возвращает
|
Разделяет заданную параметром
|
Разделяет заданную параметром
xx NULL zz
|
Возвращает начальную позицию первого вхождения
|
Извлекает из
|
Преобразует
|
Преобразует число в шестнадцатеричное представление.
|
Заменяет каждый символ в
|
Преобразует спецпоследовательности Unicode в аргументе. Символы Unicode могут быть заданы как Когда кодировка сервера — не UTF-8, символ с кодом, заданным этой спецпоследовательностью, преобразуется в фактическую кодировку сервера; если такое преобразование невозможно, выдаётся ошибка. Эта функция предоставляет (нестандартную) альтернативу строковым константам со спецпоследовательностями Unicode (см. Подраздел 4.1.2.3).
|
Функции concat
, concat_ws
и format
принимают переменное число аргументов, так что им для объединения или форматирования можно передавать значения в виде массива, помеченного ключевым словом VARIADIC
(см. Подраздел 38.5.6). Элементы такого массива обрабатываются, как если бы они были обычными аргументами функции. Если вместо массива в соответствующем аргументе передаётся NULL, функции concat
и concat_ws
возвращают NULL, а format
воспринимает NULL как массив нулевого размера.
Также обратите внимание на агрегатную функцию string_agg
в Разделе 9.21 и функции для преобразования текста в bytea
и наоборот в Таблице 9.13.
format
Функция format
выдаёт текст, отформатированный в соответствии со строкой формата, подобно функции sprintf
в C.
format
(formatstr
text
[,formatarg
"any"
[, ...] ])
formatstr
— строка, определяющая, как будет форматироваться результат. Обычный текст в строке формата непосредственно копируется в результат, за исключением спецификаторов формата. Спецификаторы формата представляют собой местозаполнители, определяющие, как должны форматироваться и выводиться в результате аргументы функции. Каждый аргумент formatstr
преобразуется в текст по правилам вывода своего типа данных, а затем форматируется и вставляется в результирующую строку согласно спецификаторам формата.
Спецификаторы формата предваряются символом %
и имеют форму
%[position
][flags
][width
]type
Здесь:
position
(необязательный)Строка вида
, где n
$n
— индекс выводимого аргумента. Индекс, равный 1, выбирает первый аргумент после formatstr
. Если position
опускается, по умолчанию используется следующий аргумент по порядку.
flags
(необязательный)Дополнительные флаги, управляющие форматированием данного спецификатора. В настоящее время поддерживается только знак минус (-
), который выравнивает результат спецификатора по левому краю. Этот флаг работает, только если также определено поле width
.
width
(необязательный)Задаёт минимальное число символов, которое будет занимать результат данного спецификатора. Выводимое значение выравнивается по правой или левой стороне (в зависимости от флага -
) с дополнением необходимым числом пробелов. Если ширина слишком мала, она просто игнорируется, т. е. результат не усекается. Ширину можно обозначить положительным целым, звёздочкой (*
), тогда ширина будет получена из следующего аргумента функции, или строкой вида *
, тогда ширина будет задаваться в n
$n
-ом аргументе функции.
Если ширина передаётся в аргументе функции, этот аргумент выбирается до аргумента, используемого для спецификатора. Если аргумент ширины отрицательный, результат выравнивается по левой стороне (как если бы был указан флаг -
) в рамках поля длины abs
(width
).
type
(необязательный)Тип спецификатора определяет преобразование соответствующего выводимого значения. Поддерживаются следующие типы:
s
форматирует значение аргумента как простую строку. Значение NULL представляется пустой строкой.
I
обрабатывает значение аргумента как SQL-идентификатор, при необходимости заключая его в кавычки. Значение NULL для такого преобразования считается ошибочным (так же, как и для quote_ident
).
L
заключает значение аргумента в апострофы, как строку SQL. Значение NULL выводится буквально, как NULL
, без кавычек (так же, как и с quote_nullable
).
В дополнение к спецификаторам, описанным выше, можно использовать спецпоследовательность %%
, которая просто выведет символ %
.
Несколько примеров простых преобразований формата:
SELECT format('Hello %s', 'World'); Результат:Hello World
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three'); Результат:Testing one, two, three, %
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly'); Результат:INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files'); Результат:INSERT INTO locations VALUES('C:\Program Files')
Следующие примеры иллюстрируют использование поля width
и флага -
:
SELECT format('|%10s|', 'foo'); Результат:| foo|
SELECT format('|%-10s|', 'foo'); Результат:|foo |
SELECT format('|%*s|', 10, 'foo'); Результат:| foo|
SELECT format('|%*s|', -10, 'foo'); Результат:|foo |
SELECT format('|%-*s|', 10, 'foo'); Результат:|foo |
SELECT format('|%-*s|', -10, 'foo'); Результат:|foo |
Эти примеры показывают применение полей position
:
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three'); Результат:Testing three, two, one
SELECT format('|%*2$s|', 'foo', 10, 'bar'); Результат:| bar|
SELECT format('|%1$*2$s|', 'foo', 10, 'bar'); Результат:| foo|
В отличие от стандартной функции C sprintf
, функция format
в PostgreSQL позволяет комбинировать в одной строке спецификаторы с полями position
и без них. Спецификатор формата без поля position
всегда использует следующий аргумент после последнего выбранного. Кроме того, функция format
не требует, чтобы в строке формата использовались все аргументы функции. Пример этого поведения:
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Результат: Testing three, two, three
Спецификаторы формата %I
и %L
особенно полезны для безопасного составления динамических операторов SQL. См. Пример 43.1.