Строковые функции и операторы
В этом разделе описываются функции и операторы для проверки и манипулирования строковыми значениями. Строки в этом контексте включают значения типов character, character varying и text. Если не указано иное, эти функции и операторы объявлены принимающими и возвращающими тип text. Также они будут взаимозаменяемо принимать аргументы типа character varying. А значения типа character до вызова функции или оператора будут преобразовываться в text, в результате чего все завершающие пробелы в значении character будут обрезаться.
В SQL определены некоторые строковые функции, в которых для разделения аргументов вместо запятых используются ключевые слова. Подробнее эти слова описаны в Таблице 9. QHB также предоставляет версии этих функций, использующие обычный синтаксис вызова функций (см. Таблицу 10).
Примечание
Оператор конкатенации строк (||
) примет нестроковый аргумент, если хотя бы один аргумент имеет строковый тип, как показано в Таблице 9. В других случаях, чтобы оператор принял нестроковый аргумент, можно использовать явное преобразование в text.
Таблица 9. Строковые функции и операторы SQL
Функция/оператор |
||
---|---|---|
Описание Пример(ы) |
||
text || text → text |
||
Конкатенирует две строки.
|
||
text || anynonarray → text anynonarray || text → text |
||
Преобразует нестроковый аргумент в текст, а затем конкатенирует две строки. (Нестроковый аргумент не может быть типом-массивом, поскольку это может вызвать неоднозначность с операторами массивов
|
||
text IS [NOT] [форма] NORMALIZED → boolean |
||
Проверяет, соответствует ли строка определенной форме нормализации Unicode. Форма указывается в необязательном ключевом слове форма: NFC (по умолчанию), NFD, NFKC или NFKD. Это выражение можно использовать, только если кодировка сервера — UTF8. Обратите внимание, что проверка нормализации с помощью этого выражения зачастую бывает быстрее, чем нормализация уже, возможно, нормализованных строк.
|
||
bit_length ( text ) → integer |
||
Возвращает количество битов в строке (это число в 8 раз больше octet_length).
|
||
char_length ( text ) → integer character_length ( text ) → integer |
||
Возвращает количество символов в строке.
|
||
lower ( text ) → text |
||
Переводит символы строки в нижний регистр в соответствии с правилами локали базы данных.
|
||
normalize ( text [, форма ] ) → text |
||
Преобразует строку в заданную форму нормализации Unicode. Форма указывается в необязательном ключевом слове форма: NFC (по умолчанию), NFD, NFKC или NFKD. Эту функцию можно использовать, только если кодировка сервера — UTF8.
|
||
octet_length ( text ) → integer |
||
Возвращает количество байтов в строке.
|
||
octet_length ( character ) → integer |
||
Возвращает количество байтов в строке. Поскольку эта версия функции принимает непосредственно тип character, она не обрезает пробелы, завершающие строку.
|
||
overlay ( строка text PLACING новая_подстрока text FROM начало integer [FOR число integer] ) → text |
||
Заменяет в строке подстроку, начинающуюся с символа на позиции начало и длиной в заданное число символов, на новую_подстроку. Если число опущено, по умолчанию количество заменяемых символов определяется длиной новой_подстроки.
|
||
position ( подстрока text IN строка text ) → integer |
||
Возвращает начальную позицию первого вхождения подстроки в строке или ноль, если такого вхождения нет.
|
||
substring ( строка text [ FROM начало integer ] [ FOR число integer ] ) → text |
||
Извлекает из строки подстроку, начиная с символа на позиции начало (если таковая указана) и останавливаясь после заданного числа символов (если оно указано). Должен присутствовать хотя бы один из аргументов: начало или число.
|
||
substring ( строка text FROM шаблон text ) → text |
||
Извлекает первую подстроку, соответствующую регулярному выражению POSIX; см. подраздел Регулярные выражения POSIX.
|
||
substring ( строка text SIMILAR шаблон text ESCAPE управляющий_символ text ) → text substring ( строка text FROM шаблон text FOR управляющий_символ text ) → text |
||
Извлекает первую подстроку, соответствующую регулярному выражению SQL; см. подраздел Регулярные выражения SIMILAR TO. Первая форма была определена в стандарте, начиная с SQL:2003; вторая форма существовала только в стандарте SQL:1999, и ее следует считать устаревшей.
|
||
trim ( [LEADING | TRAILING | BOTH] [символы text] FROM строка text ) → text |
||
Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы), с начала (LEADING), конца (TRAILING) или с обеих сторон (BOTH) (по умолчанию — BOTH) строки.
|
||
trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] строка text [, символы text ] ) → text |
||
Это нестандартный синтаксис для trim().
|
||
upper ( text ) → text |
||
Переводит символы строки в верхний регистр в соответствии с правилами локали базы данных.
|
Кроме этого в QHB имеются дополнительные функции для работы со строками, перечисленные в Таблице 10. Некоторые из них используются для внутренней реализации стандартных строковых функций SQL, перечисленных в Таблице 9.
Таблица 10. Другие строковые функции
Функция |
||
---|---|---|
Описание Пример(ы) |
||
ascii ( text ) → integer |
||
Возвращает числовой код первого символа аргумента. Для кодировки UTF8 возвращает код символа в Unicode. Для других многобайтовых кодировок аргумент должен быть символом ASCII.
|
||
btrim ( строка text [, символы text ] ) → text |
||
Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы) с начала и конца строки.
|
||
chr ( integer ) → text |
||
Возвращает символ с заданным кодом. Для кодировки UTF8 возвращает код символа в Unicode. Для других многобайтовых кодировок аргумент должен быть символом ASCII. Эта функция не может выдать chr(0), поскольку этот символ нельзя сохранить в текстовых типах данных.
|
||
concat ( значение1 "any" [, значение2 "any" [, ...] ] ) → text |
||
Конкатенирует текстовые представления всех аргументов. Аргументы NULL игнорируются.
|
||
concat_ws ( разделитель text, значение1 "any" [, значение2 "any" [, ...] ] ) → text |
||
Конкатенирует все аргументы, кроме первого, с разделителями. Первый аргумент используется как строка-разделитель и должен быть отличен от NULL. Другие аргументы NULL игнорируются.
|
||
format ( строка_формата text [, форматируемые_аргументы "any" [, ...] ] ) → text |
||
Форматирует аргументы в соответствии со строкой формата; см. подраздел format. Эта функция схожа с функцией sprintf в языке C.
|
||
initcap ( text ) → text |
||
Переводит первую букву каждого слова в верхний регистр, а остальные — в нижний. Словами являются последовательности буквенно-цифровых символов, разделенных любыми отличными от них символами.
|
||
left ( строка text, n integer ) → text |
||
Возвращает первые n символов в строке или, если n — отрицательное число, возвращает все символы, кроме последних |n| символов.
|
||
length ( text ) → integer |
||
Возвращает количество символов в строке.
|
||
lpad ( строка text, длина integer [, заполнитель text ] ) → text |
||
Расширяет строку до заданной длины путем добавления слева символов-заполнителей (по умолчанию пробелов). Если строка уже длиннее заданной длины, она обрезается (справа).
|
||
ltrim ( строка text [, символы text ] ) → text |
||
Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы) с начала строки.
|
||
md5 ( text ) → text |
||
Вычисляет MD5-хеш аргумента с результатом, написанным в шестнадцатеричном формате.
|
||
parse_ident ( полный_идентификатор text [, строгий_режим boolean DEFAULT true ] ) → text[] |
||
Разделяет полный_идентификатор на массив идентификаторов, удаляя кавычки, в которые заключены отдельные идентификаторы. По умолчанию лишние символы после последнего идентификатора считаются ошибкой, но если установить второй параметр в false, то такие лишние символы игнорируются. (Это поведение полезно для синтаксического анализа имен таких объектов, как функции.) Обратите внимание, что эта функция не усекает чрезмерно длинные идентификаторы. Если есть необходимость в усеченных именах, можно привести результат к
|
||
pg_client_encoding ( ) → name |
||
Возвращает имя текущей кодировки клиента.
|
||
quote_ident ( text ) → text |
||
Возвращает заданную строку, надлежащим образом подготовленную для использования в качестве идентификатора в строке оператора SQL. Кавычки добавляются только при необходимости (т. е. если строка содержит символы, недопустимые в идентификаторе, или буквы в разном регистре). Кавычки, уже содержащиеся в строке, дублируются. См. также пример Заключение в кавычки значений в динамических запросах.
|
||
quote_literal ( text ) → text |
||
Возвращает заданную строку, надлежащим образом подготовленную для использования в качестве строкового литерала в строке оператора SQL. Апострофы и обратные слэши, уже содержащиеся в строке, дублируются. Обратите внимание, что quote_literal возвращает NULL, когда входящее значение NULL; если аргумент может оказаться NULL, то зачастую лучше вызвать quote_nullable. См также пример Заключение в кавычки значений в динамических запросах.
|
||
quote_literal ( anyelement ) → text |
||
Преобразует заданное значение в текст, а затем заключает в апострофы как литерал. Апострофы и обратные слэши, уже содержащиеся в значении, дублируются.
|
||
quote_nullable ( text ) → text |
||
Возвращает заданную строку, подходящим образом подготовленную для использования в качестве строкового литерала в строке оператора SQL, или, если аргумент NULL, возвращает NULL. Апострофы и обратные слэши, уже содержащиеся в строке, дублируются. См. также пример Заключение в кавычки значений в динамических запросах.
|
||
quote_nullable ( anyelement ) → text |
||
Преобразует заданное значение в текст, а затем заключает в апострофы как литерал или, если аргумент NULL, возвращает NULL. Апострофы и обратные слэши, уже содержащиеся в значении, дублируются.
|
||
regexp_match ( строка text, шаблон text [, флаги text ] ) → text[] |
||
Возвращает фиксированные подстроки, полученные из первого вхождения регулярного выражения POSIX в строке; см. подраздел Регулярные выражения POSIX.
|
||
regexp_matches ( строка text, шаблон text [, флаги text ] ) → setof text[] |
||
Возвращает фиксированные подстроки, полученные из первого вхождения регулярного выражения POSIX в строке или из нескольких вхождений, если используется флаг g; см. подраздел Регулярные выражения POSIX.
|
||
regexp_replace ( строка text, шаблон text, подстановка text [, флаги text ] ) → text |
||
Заменяет подстроки, полученные из первого вхождения регулярного выражения POSIX, или несколько вхождений подстрок, если используется флаг g; см. подраздел Регулярные выражения POSIX.
|
||
regexp_split_to_array ( строка text, шаблон text [, флаги text ] ) → text[] |
||
Разделяет строку, используя в качестве разделителя регулярное выражение POSIX и выдавая массив результатов; см. подраздел Регулярные выражения POSIX.
|
||
regexp_split_to_table ( строка text, шаблон text [, флаги text ] ) → setof text |
||
Разделяет строку, используя в качестве разделителя регулярное выражение POSIX и выдавая набор результатов; см. подраздел Регулярные выражения POSIX.
|
||
repeat ( строка text, число integer ) → text |
||
Повторяет строку заданное число раз.
|
||
replace ( строка text, цель text, подстановка text ) → text |
||
Заменяет все вхождения в строке подстроки цель подстрокой подстановка.
|
||
reverse ( text ) → text |
||
Переставляет символы в строке в обратном порядке.
|
||
right ( строка text, n integer ) → text |
||
Возвращает последние n символов в строке или, когда n — отрицательное число, возвращает все символы, кроме первых |n| символов.
|
||
rpad ( строка text, длина integer [, заполнитель text ] ) → text |
||
Расширяет строку до заданной длины путем добавления справа символов-заполнителей (по умолчанию пробелов). Если строка уже длиннее заданной длины, она обрезается (слева).
|
||
rtrim ( строка text [, символы text ] ) → text |
||
Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы), с конца строки.
|
||
split_part ( строка text, разделитель text, n integer ) → text |
||
Разделяет строку по разделителю и возвращает n-ое поле (считая с первого) или, когда n — отрицательное число, возвращает |n|-ое поле с конца.
|
||
strpos ( строка text, подстрока text ) → integer |
||
Возвращает начальную позицию первого вхождения заданной подстроки в строке или ноль, если такого вхождения нет. (То же, что и функция position(подстрока in строка), но обратите внимание на обратный порядок аргументов.)
|
||
substr ( строка text, начало integer [, число integer ] ) → text |
||
Извлекает из строки подстроку, начинающуюся с символа на позиции начало и длиной в заданное число символов, если оно указано. (То же, что и функция substring(строка from начало for число).)
|
||
starts_with ( строка text, префикс text ) → boolean |
||
Возвращает true, если строка начинается с префикса.
|
||
string_to_array ( строка text, разделитель text [, нулевая_строка text ] ) → text[] |
||
Разделяет строку по разделителю и формирует из результирующих полей массив типа text. Если разделитель равен NULL, каждый символ в строке станет отдельным элементом массива. Если разделителем является пустая строка, то вся строка рассматривается как одно поле. Если задана нулевая_строка и она отлична от NULL, то поля, соответствующие этой строке, заменяются значениями NULL.
|
||
string_to_table ( строка text, разделитель text [, нулевая_строка text ] ) → setof text |
||
Разделяет строку по разделителю и возвращает результирующие поля в виде набора текстовых строк. Если разделитель равен NULL, каждый символ в строке станет отдельной строкой результата. Если в разделителе передается пустая строка, то строка рассматривается как одно поле. Если задана нулевая_строка и она отлична от NULL, то поля, соответствующие этой строке, заменяются значениями NULL.
|
||
to_ascii ( строка text ) → text to_ascii ( строка text, кодировка name ) → text to_ascii ( строка text, кодировка integer ) → text |
||
Преобразует строку в ASCII из другой кодировки, которая может задаваться по имени или по номеру. Если кодировка опущена, подразумевается кодировка базы данных (что на практике является единственным полезным вариантом). В основном преобразование сводится к отбрасыванию диакритических знаков. Преобразование поддерживается только для кодировок LATIN1, LATIN2, LATIN9 и WIN1250. (Другое, более гибкое, решение реализовано в модуле unaccent.)
|
||
to_hex ( integer ) → text to_hex ( bigint ) → text |
||
Преобразует число в равнозначное ему шестнадцатеричное представление.
|
||
translate ( строка text, цель text, подстановка text ) → text |
||
Заменяет каждый символ в строке, совпадающий с символом из множества цель, на соответствующий символ в множестве подстановка. Если цель длиннее подстановки, вхождения лишних символов цели удаляются.
|
||
unistr ( text ) → text |
||
Определяет управляющие символы Unicode в аргументе. Символы Unicode можно задать в виде \XXXX (4 шестнадцатеричные цифры), \+XXXXXX (6 шестнадцатеричных цифр), \uXXXX (4 шестнадцатеричные цифры) или \UXXXXXXXX (8 шестнадцатеричных цифр). Чтобы указать обратный слэш, напишите два обратных слэша. Все остальные символы берутся буквально.
|
Функции 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. См. пример Заключение в кавычки значений в динамических запросах.