Строковые функции и операторы

В этом разделе описываются функции и операторы для проверки и манипулирования строковыми значениями. Строки в этом контексте включают значения типов character, character varying и text. Если не указано иное, эти функции и операторы объявлены принимающими и возвращающими тип text. Также они будут взаимозаменяемо принимать аргументы типа character varying. А значения типа character до вызова функции или оператора будут преобразовываться в text, в результате чего все завершающие пробелы в значении character будут обрезаться.

В SQL определены некоторые строковые функции, в которых для разделения аргументов вместо запятых используются ключевые слова. Подробнее эти слова описаны в Таблице 9. QHB также предоставляет версии этих функций, использующие обычный синтаксис вызова функций (см. Таблицу 10).

Примечание
Оператор конкатенации строк (||) примет нестроковый аргумент, если хотя бы один аргумент имеет строковый тип, как показано в Таблице 9. В других случаях, чтобы оператор принял нестроковый аргумент, можно использовать явное преобразование в text.

Таблица 9. Строковые функции и операторы SQL

Кроме этого в QHB имеются дополнительные функции для работы со строками, перечисленные в Таблице 10. Некоторые из них используются для внутренней реализации стандартных строковых функций SQL, перечисленных в Таблице 9.

Таблица 10. Другие строковые функции

regexp_matches('foobarbequebaz', 'ba.', 'g') →
    {bar}
    {baz}

regexp_split_to_table('hello world', '\s+') →
    hello
    world

string_to_table('xx~^~yy~^~zz', '~^~', 'yy') →
    xx
    NULL
    zz

Функции concat, concat_ws и format принимают переменное число аргументов, поэтому им можно передавать значения для конкатенирования или форматирования в виде массива, помеченного ключевым словом VARIADIC (см. раздел Функции SQL с переменным числом аргументов). Элементы такого массива обрабатываются так, как если бы они были отдельными обычными аргументами функции. Если вместо массива в аргументе передается NULL, функции concat и concat_ws возвращают NULL, а format воспринимает NULL как массив с нулевым элементом.

Также обратите внимание на агрегатную функцию string_agg в разделе Агрегатные функции и функции для преобразования текста в bytea и наоборот в таблице Функции для преобразования текстовых/двоичных строк.

format

Функция format создает выходные данные, отформатированные в соответствии со строкой формата, в стиле, аналогичном функции C sprintf.

format(строка_формата text [, форматируемые_аргументы "any" [, ...] ])

Аргумент строка\ формата определяет, как результат должен быть отформатирован. Текст в строке формата копируется непосредственно в результат, кроме случаев, когда используются спецификаторы формата. Спецификаторы формата действуют как заполнители в строке, определяя, как именно последующие аргументы функции должны быть отформатированы и вставлены в результат. Каждый форматируемый_аргумент преобразуется в текст в соответствии с обычными правилами вывода для его типа данных, а затем форматируется и вставляется в результирующую строку в соответствии со спецификаторами формата.

Спецификаторы формата вводятся символом % и имеют форму

%[позиция][флаги][ширина]тип

где полями компонентов являются:

позиция (необязательный)
Строка вида n$, где n — индекс выводимого аргумента. Индекс, равный 1, означает первый аргумент после formatstr. Если позиция опущена, по умолчанию используется следующий аргумент в последовательности.

флаги (необязательный)
Дополнительные параметры, управляющие форматированием вывода спецификатора формата. В настоящее время единственным поддерживаемым флагом является знак минус (-), который приведет к выравниванию вывода спецификатора формата по левому краю. Этот флаг не имеет никакого эффекта, если не указано поле ширина.

ширина (необязательный)
Задает минимальное количество символов, которое используется для отображения выходных данных спецификатора формата. Выходные данные дополняются слева или справа (в зависимости от флага -) пробелами, необходимыми для заполнения до заданной ширины. Слишком маленькая ширина не приводит к усечению вывода, а просто игнорируется. Ширина можно указать любым из следующих способов: положительным целым числом, звездочкой (*) для использования в качестве ширины следующего аргумента функции или строкой вида *n$ для использования в качестве ширины n-го аргумента функции.

Если ширина берется из аргумента функции, этот аргумент задействуется раньше аргумента, используемого для значения спецификатора формата. Если аргумент ширины отрицательный, результат выравнивается по левому краю (как если бы был указан флаг -) в поле длиной abs(width).

тип (обязательный)
Тип преобразования формата, используемый для создания выходных данных спецификатора формата. Поддерживаются следующие типы:

• 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')

Вот примеры использования полей ширины и флага -:

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       |

Эти примеры показывают использование полей позиции:

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 в QHB позволяет сочетать в одной строке формата спецификаторы формата с полями позиции и без них. Спецификатор формата без поля позиции всегда использует следующий аргумент после последнего задействованного аргумента. Кроме того, функция format не требует использования в строке формата всех аргументов функции. Например:

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Результат: Testing three, two, three

Спецификаторы формата %I и %L особенно полезны для безопасного построения динамических операторов SQL. См. пример Заключение в кавычки значений в динамических запросах.