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.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.
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. См. Пример 43.1.