9.4. Строковые функции и операторы
В этом разделе описаны функции и операторы для работы с текстовыми строками. Под строками в данном контексте подразумеваются значения типов character, character varying и text. Если не отмечено обратное, эти функции и операторы принимают и возвращают тип text. С тем же успехом в аргументах может передаваться тип character varying. Аргументы же типа character до вызова оператора или функции приводятся к типу text, вследствие чего завершающие пробелы в значении character будут обрезаться.
В SQL определены несколько строковых функций, в которых аргументы разделяются не запятыми, а ключевыми словами. Они перечислены в Таблице 9.9. Postgres Pro также предоставляет варианты этих функций с синтаксисом, обычным для функций (см. Таблицу 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.10. Другие строковые функции
Функция Описание Пример(ы) |
|---|
Возвращает числовой код первого символа аргумента. Для UTF8 возвращает код символа в Unicode. Для других многобайтных кодировок аргумент должен быть ASCII-символом.
|
Удаляет наибольшую подстроку, содержащую только символы
|
Возвращает символ с данным кодом. Для UTF8 аргумент воспринимается как код символа Unicode, а для других кодировок он должен указывать на ASCII-символ. Эта функция не может выдать
|
Соединяет текстовые представления всех аргументов, игнорируя NULL.
|
Соединяет вместе все аргументы, кроме первого, через разделитель. Разделитель задаётся в первом аргументе и должен быть отличен от NULL. В других аргументах значение NULL игнорируется.
|
Форматирует аргументы в соответствии со строкой формата; см. Подраздел 9.4.1. Эта функция работает подобно
|
Переводит первую букву каждого слова в строке в верхний регистр, а остальные — в нижний. Словами считаются последовательности алфавитно-цифровых символов, разделённые любыми другими символами.
|
Возвращает первые
|
Возвращает число символов в строке.
|
Дополняет строку
|
Удаляет наибольшую подстроку, содержащую только символы
|
Вычисляет MD5-хеш аргумента и выдаёт результат в шестнадцатеричном виде.
|
Раскладывает полный идентификатор, задаваемый параметром
|
Возвращает имя текущей клиентской кодировки.
|
Преобразует аргумент в строку, подходящую для использования в качестве идентификатора в SQL-операторе. При необходимости идентификатор заключается в кавычки (например, если он содержит символы, недопустимые в открытом виде, или буквы в разных регистрах). Если переданная строка содержит кавычки, они дублируются. См. также Пример 44.1.
|
Преобразует аргумент в строку, подходящую для использования в качестве текстовой константы в SQL-операторе. Внутренние символы апостроф и обратная косая черта при этом дублируются. Заметьте, что
|
Переводит данное значение в текстовый вид и заключает в апострофы как текстовую строку. Символы апостроф и обратная косая черта при этом дублируются.
|
Преобразует аргумент в строку, подходящую для использования в качестве текстовой константы в SQL-операторе; при этом для аргумента NULL возвращается строка
|
Переводит данное значение в текстовый вид и заключает в апострофы как текстовую строку, при этом для аргумента NULL возвращается строка
|
Возвращает подходящие подстроки, полученные из первого вхождения регулярного выражения POSIX в строке
|
Возвращает подходящие подстроки, полученные из первого вхождения регулярного выражения POSIX в строке
{bar}
{baz} |
Заменяет подходящие подстроки, полученные из первого вхождения регулярного выражения POSIX или подходящие подстроки из нескольких вхождений, если использован флаг
|
Разделяет содержимое
|
Разделяет содержимое
hello world |
Повторяет содержимое
|
Заменяет все вхождения в
|
Переставляет символы в строке в обратном порядке.
|
Возвращает последние
|
Дополняет строку
|
Удаляет наибольшую подстроку, содержащую только символы
|
Разделяет строку
|
Возвращает начальную позицию первого вхождения
|
Извлекает из
|
Возвращает
|
Разделяет заданную параметром
|
Разделяет заданную параметром
xx NULL zz |
Преобразует
|
Преобразует число в шестнадцатеричное представление.
|
Заменяет каждый символ в
|
Преобразует спецпоследовательности Unicode в аргументе. Символы Unicode могут быть заданы как Когда кодировка сервера — не UTF-8, символ с кодом, заданным этой спецпоследовательностью, преобразуется в фактическую кодировку сервера; если такое преобразование невозможно, выдаётся ошибка. Эта функция предоставляет (нестандартную) альтернативу строковым константам со спецпоследовательностями Unicode (см. Подраздел 4.1.2.3).
|
Функции concat, concat_ws и format принимают переменное число аргументов, так что им для объединения или форматирования можно передавать значения в виде массива, помеченного ключевым словом VARIADIC (см. Подраздел 39.5.6). Элементы такого массива обрабатываются, как если бы они были обычными аргументами функции. Если вместо массива в соответствующем аргументе передаётся NULL, функции concat и concat_ws возвращают NULL, а format воспринимает NULL как массив нулевого размера.
Также обратите внимание на агрегатную функцию string_agg в Разделе 9.21 и функции для преобразования текста в bytea и наоборот в Таблице 9.13.
9.4.1. format
Функция format выдаёт текст, отформатированный в соответствии со строкой формата, подобно функции sprintf в C.
format(formatstrtext[,formatarg"any"[, ...] ])
formatstr — строка, определяющая, как будет форматироваться результат. Обычный текст в строке формата непосредственно копируется в результат, за исключением спецификаторов формата. Спецификаторы формата представляют собой местозаполнители, определяющие, как должны форматироваться и выводиться в результате аргументы функции. Каждый аргумент formatarg преобразуется в текст по правилам вывода своего типа данных, а затем форматируется и вставляется в результирующую строку согласно спецификаторам формата.
Спецификаторы формата предваряются символом % и имеют форму
%[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 в Postgres Pro позволяет комбинировать в одной строке спецификаторы с полями position и без них. Спецификатор формата без поля position всегда использует следующий аргумент после последнего выбранного. Кроме того, функция format не требует, чтобы в строке формата использовались все аргументы функции. Пример этого поведения:
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Результат: Testing three, two, three
Спецификаторы формата %I и %L особенно полезны для безопасного составления динамических операторов SQL. См. Пример 44.1.