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

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

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

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

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

Функция/оператор
	Описание Пример(ы)
text \|\| text → text
	Конкатенирует две строки. `'Quan' \|\| 'tum' → Quantum`
text \|\| anynonarray → text anynonarray \|\| text → text
	Преобразует нестроковый аргумент в текст, а затем конкатенирует две строки. (Нестроковый аргумент не может быть типом-массивом, поскольку это может вызвать неоднозначность с операторами массивов `\|\|`. Если требуется конкатенировать эквивалент текста в массиве, следует явно привести его к типу text.) `'Value: ' \|\| 42 → Value: 42`
text IS [NOT] [форма] NORMALIZED → boolean
	Проверяет, соответствует ли строка определенной форме нормализации Unicode. Форма указывается в необязательном ключевом слове форма: NFC (по умолчанию), NFD, NFKC или NFKD. Это выражение можно использовать, только если кодировка сервера — UTF8. Обратите внимание, что проверка нормализации с помощью этого выражения зачастую бывает быстрее, чем нормализация уже, возможно, нормализованных строк. `U&'\0061\0308bc' IS NFD NORMALIZED → t`
bit_length ( text ) → integer
	Возвращает количество битов в строке (это число в 8 раз больше octet_length). `bit_length('jose') → 32`
char_length ( text ) → integer character_length ( text ) → integer
	Возвращает количество символов в строке. `char_length('josé') → 4`
lower ( text ) → text
	Переводит символы строки в нижний регистр в соответствии с правилами локали базы данных. `lower('TOM') → tom`
normalize ( text [, форма ] ) → text
	Преобразует строку в заданную форму нормализации Unicode. Форма указывается в необязательном ключевом слове форма: NFC (по умолчанию), NFD, NFKC или NFKD. Эту функцию можно использовать, только если кодировка сервера — UTF8. `normalize(U&'\0061\0308bc', NFC) → U&'\00E4bc'`
octet_length ( text ) → integer
	Возвращает количество байтов в строке. `octet_length('josé') → 5` (если кодировка сервера — UTF8)
octet_length ( character ) → integer
	Возвращает количество байтов в строке. Поскольку эта версия функции принимает непосредственно тип character, она не обрезает пробелы, завершающие строку. `octet_length('abc '::character(4)) → 4`
overlay ( строка text PLACING новая_подстрока text FROM начало integer [FOR число integer] ) → text
	Заменяет в строке подстроку, начинающуюся с символа на позиции начало и длиной в заданное число символов, на новую_подстроку. Если число опущено, по умолчанию количество заменяемых символов определяется длиной новой_подстроки. `overlay('Txxxxas' placing 'hom' from 2 for 4) → Thomas`
position ( подстрока text IN строка text ) → integer
	Возвращает начальную позицию первого вхождения подстроки в строке или ноль, если такого вхождения нет. `position('om' in 'Thomas') → 3`
substring ( строка text [ FROM начало integer ] [ FOR число integer ] ) → text
	Извлекает из строки подстроку, начиная с символа на позиции начало (если таковая указана) и останавливаясь после заданного числа символов (если оно указано). Должен присутствовать хотя бы один из аргументов: начало или число. `substring('Thomas' from 2 for 3) → hom` `substring('Thomas' from 3) → omas` `substring('Thomas' for 2) → Th`
substring ( строка text FROM шаблон text ) → text
	Извлекает первую подстроку, соответствующую регулярному выражению POSIX; см. подраздел Регулярные выражения POSIX. `substring('Thomas' from '...$') → mas`
substring ( строка text SIMILAR шаблон text ESCAPE управляющий_символ text ) → text substring ( строка text FROM шаблон text FOR управляющий_символ text ) → text
	Извлекает первую подстроку, соответствующую регулярному выражению SQL; см. подраздел Регулярные выражения SIMILAR TO. Первая форма была определена в стандарте, начиная с SQL:2003; вторая форма существовала только в стандарте SQL:1999, и ее следует считать устаревшей. `substring('Thomas' similar '%#"o_a#"_' escape '#') → oma`
trim ( [LEADING \| TRAILING \| BOTH] [символы text] FROM строка text ) → text
	Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы), с начала (LEADING), конца (TRAILING) или с обеих сторон (BOTH) (по умолчанию — BOTH) строки. `trim(both 'xyz' from 'yxTomxx') → Tom`
trim ( [ LEADING \| TRAILING \| BOTH ] [ FROM ] строка text [, символы text ] ) → text
	Это нестандартный синтаксис для trim(). `trim(both 'xyz' from 'yxTomxx') → Tom`
upper ( text ) → text
	Переводит символы строки в верхний регистр в соответствии с правилами локали базы данных. `upper('tom') → TOM`

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

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

Функция
	Описание Пример(ы)
ascii ( text ) → integer
	Возвращает числовой код первого символа аргумента. Для кодировки UTF8 возвращает код символа в Unicode. Для других многобайтовых кодировок аргумент должен быть символом ASCII. `ascii('x') → 120`
btrim ( строка text [, символы text ] ) → text
	Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы) с начала и конца строки. `btrim('xyxtrimyyx', 'xyz') → trim`
chr ( integer ) → text
	Возвращает символ с заданным кодом. Для кодировки UTF8 возвращает код символа в Unicode. Для других многобайтовых кодировок аргумент должен быть символом ASCII. Эта функция не может выдать chr(0), поскольку этот символ нельзя сохранить в текстовых типах данных. `chr(65) → A`
concat ( значение1 "any" [, значение2 "any" [, ...] ] ) → text
	Конкатенирует текстовые представления всех аргументов. Аргументы NULL игнорируются. `concat('abcde', 2, NULL, 22) → abcde222`
concat_ws ( разделитель text, значение1 "any" [, значение2 "any" [, ...] ] ) → text
	Конкатенирует все аргументы, кроме первого, с разделителями. Первый аргумент используется как строка-разделитель и должен быть отличен от NULL. Другие аргументы NULL игнорируются. `concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22`
format ( строка_формата text [, форматируемые_аргументы "any" [, ...] ] ) → text
	Форматирует аргументы в соответствии со строкой формата; см. подраздел format. Эта функция схожа с функцией sprintf в языке C. `format('Hello %s, %1$s', 'World') → Hello World, World`
initcap ( text ) → text
	Переводит первую букву каждого слова в верхний регистр, а остальные — в нижний. Словами являются последовательности буквенно-цифровых символов, разделенных любыми отличными от них символами. `initcap('hi THOMAS') → Hi Thomas`
left ( строка text, n integer ) → text
	Возвращает первые n символов в строке или, если n — отрицательное число, возвращает все символы, кроме последних \|n\| символов. `left('abcde', 2) → ab`
length ( text ) → integer
	Возвращает количество символов в строке. `length('jose') → 4`
lpad ( строка text, длина integer [, заполнитель text ] ) → text
	Расширяет строку до заданной длины путем добавления слева символов-заполнителей (по умолчанию пробелов). Если строка уже длиннее заданной длины, она обрезается (справа). `lpad('hi', 5, 'xy') → xyxhi`
ltrim ( строка text [, символы text ] ) → text
	Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы) с начала строки. `ltrim('zzzytest', 'xyz') → test`
md5 ( text ) → text
	Вычисляет MD5-хэш аргумента с результатом, написанным в шестнадцатеричном формате. `md5('abc') → 900150983cd24fb0d6963f7d28e17f72`
parse_ident ( полный_идентификатор text [, строгий_режим boolean DEFAULT true ] ) → text[]
	Разделяет полный_идентификатор на массив идентификаторов, удаляя кавычки, в которые заключены отдельные идентификаторы. По умолчанию лишние символы после последнего идентификатора считаются ошибкой, но если установить второй параметр в false, то такие лишние символы игнорируются. (Это поведение полезно для синтаксического анализа имен таких объектов, как функции.) Обратите внимание, что эта функция не усекает чрезмерно длинные идентификаторы. Если есть необходимость в усеченных именах, можно привести результат к `name[]`. `parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable}`
pg_client_encoding ( ) → name
	Возвращает имя текущей кодировки клиента. `pg_client_encoding() → UTF8`
quote_ident ( text ) → text
	Возвращает заданную строку, надлежащим образом подготовленную для использования в качестве идентификатора в строке оператора SQL. Кавычки добавляются только при необходимости (т. е. если строка содержит символы, недопустимые в идентификаторе, или буквы в разном регистре). Кавычки, уже содержащиеся в строке, дублируются. См. также пример Заключение в кавычки значений в динамических запросах. `quote_ident('Foo bar') → "Foo bar"`
quote_literal ( text ) → text
	Возвращает заданную строку, надлежащим образом подготовленную для использования в качестве строкового литерала в строке оператора SQL. Апострофы и обратные слэши, уже содержащиеся в строке, дублируются. Обратите внимание, что quote_literal возвращает NULL, когда входящее значение NULL; если аргумент может оказаться NULL, то зачастую лучше вызвать quote_nullable. См также пример Заключение в кавычки значений в динамических запросах. `quote_literal(E'O\'Reilly') → 'O''Reilly'`
quote_literal ( anyelement ) → text
	Преобразует заданное значение в текст, а затем заключает в апострофы как литерал. Апострофы и обратные слэши, уже содержащиеся в значении, дублируются. `quote_literal(42.5) → '42.5'`
quote_nullable ( text ) → text
	Возвращает заданную строку, подходящим образом подготовленную для использования в качестве строкового литерала в строке оператора SQL, или, если аргумент NULL, возвращает NULL. Апострофы и обратные слэши, уже содержащиеся в строке, дублируются. См. также пример Заключение в кавычки значений в динамических запросах. `quote_nullable(NULL) → NULL`
quote_nullable ( anyelement ) → text
	Преобразует заданное значение в текст, а затем заключает в апострофы как литерал или, если аргумент NULL, возвращает NULL. Апострофы и обратные слэши, уже содержащиеся в значении, дублируются. `quote_nullable(42.5) → '42.5'`
regexp_match ( строка text, шаблон text [, флаги text ] ) → text[]
	Возвращает фиксированные подстроки, полученные из первого вхождения регулярного выражения POSIX в строке; см. подраздел Регулярные выражения POSIX. `regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}`
regexp_matches ( строка text, шаблон text [, флаги text ] ) → setof text[]
	Возвращает фиксированные подстроки, полученные из первого вхождения регулярного выражения POSIX в строке или из нескольких вхождений, если используется флаг g; см. подраздел Регулярные выражения POSIX. `regexp_matches('foobarbequebaz', 'ba.', 'g') → {bar} {baz}`
regexp_replace ( строка text, шаблон text, подстановка text [, флаги text ] ) → text
	Заменяет подстроки, полученные из первого вхождения регулярного выражения POSIX, или несколько вхождений подстрок, если используется флаг g; см. подраздел Регулярные выражения POSIX. `regexp_replace('Thomas', '.[mN]a.', 'M') → ThM`
regexp_split_to_array ( строка text, шаблон text [, флаги text ] ) → text[]
	Разделяет строку, используя в качестве разделителя регулярное выражение POSIX и выдавая массив результатов; см. подраздел Регулярные выражения POSIX. `regexp_split_to_array('hello world', '\s+') → {hello,world}`
regexp_split_to_table ( строка text, шаблон text [, флаги text ] ) → setof text
	Разделяет строку, используя в качестве разделителя регулярное выражение POSIX и выдавая набор результатов; см. подраздел Регулярные выражения POSIX. `regexp_split_to_table('hello world', '\s+') → hello world`
repeat ( строка text, число integer ) → text
	Повторяет строку заданное число раз. `repeat('Pg', 4) → PgPgPgPg`
replace ( строка text, цель text, подстановка text ) → text
	Заменяет все вхождения в строке подстроки цель подстрокой подстановка. `replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef`
reverse ( text ) → text
	Переставляет символы в строке в обратном порядке. `reverse('abcde') → edcba`
right ( строка text, n integer ) → text
	Возвращает последние n символов в строке или, когда n — отрицательное число, возвращает все символы, кроме первых \|n\| символов. `right('abcde', 2) → de`
rpad ( строка text, длина integer [, заполнитель text ] ) → text
	Расширяет строку до заданной длины путем добавления справа символов-заполнителей (по умолчанию пробелов). Если строка уже длиннее заданной длины, она обрезается (слева). `rpad('hi', 5, 'xy') → hixyx`
rtrim ( строка text [, символы text ] ) → text
	Удаляет самую длинную подстроку, содержащую только заданные символы (по умолчанию пробелы), с конца строки. `rtrim('testxxzx', 'xyz') → test`
split_part ( строка text, разделитель text, n integer ) → text
	Разделяет строку по разделителю и возвращает n-ое поле (считая с первого) или, когда n — отрицательное число, возвращает \|n\|-ое поле с конца. `split_part('abc~@~def~@~ghi', '~@~', 2) → def` `split_part('abc,def,ghi,jkl', ',', -2) → ghi`
strpos ( строка text, подстрока text ) → integer
	Возвращает начальную позицию первого вхождения заданной подстроки в строке или ноль, если такого вхождения нет. (То же, что и функция position(подстрока in строка), но обратите внимание на обратный порядок аргументов.) `strpos('high', 'ig') → 2`
*substr ( строка* text, начало integer [, число integer ] ) → text**
	Извлекает из строки подстроку, начинающуюся с символа на позиции начало и длиной в заданное число символов, если оно указано. (То же, что и функция substring(строка from начало for число).) `substr('alphabet', 3) → phabet` `substr('alphabet', 3, 2) → ph`
starts_with ( строка text, префикс text ) → boolean
	Возвращает true, если строка начинается с префикса. `starts_with('alphabet', 'alph') → t`
string_to_array ( строка text, разделитель text [, нулевая_строка text ] ) → text[]
	Разделяет строку по разделителю и формирует из результирующих полей массив типа text. Если разделитель равен NULL, каждый символ в строке станет отдельным элементом массива. Если разделителем является пустая строка, то вся строка рассматривается как одно поле. Если задана нулевая_строка и она отлична от NULL, то поля, соответствующие этой строке, заменяются значениями NULL. `string_to_array('xx~yy~zz', '~', 'yy') → {xx,NULL,zz}`
string_to_table ( строка text, разделитель text [, нулевая_строка text ] ) → setof text
	Разделяет строку по разделителю и возвращает результирующие поля в виде набора текстовых строк. Если разделитель равен NULL, каждый символ в строке станет отдельной строкой результата. Если в разделителе передается пустая строка, то строка рассматривается как одно поле. Если задана нулевая_строка и она отлична от NULL, то поля, соответствующие этой строке, заменяются значениями NULL. `string_to_table('xx~^~yy~^~zz', '~^~', 'yy') → xx NULL zz`
*to_ascii ( строка* text ) → text to_ascii ( строка text, кодировка name ) → text to_ascii ( строка text, кодировка integer ) → text**
	Преобразует строку в ASCII из другой кодировки, которая может задаваться по имени или по номеру. Если кодировка опущена, подразумевается кодировка базы данных (что на практике является единственным полезным вариантом). В основном преобразование сводится к отбрасыванию диакритических знаков. Преобразование поддерживается только для кодировок LATIN1, LATIN2, LATIN9 и WIN1250. (Другое, более гибкое, решение реализовано в модуле unaccent.) `to_ascii('Karél') → Karel`
to_hex ( integer ) → text to_hex ( bigint ) → text
	Преобразует число в равнозначное ему шестнадцатеричное представление. `to_hex(2147483647) → 7fffffff`
translate ( строка text, цель text, подстановка text ) → text
	Заменяет каждый символ в строке, совпадающий с символом из множества цель, на соответствующий символ в множестве подстановка. Если цель длиннее подстановки, вхождения лишних символов цели удаляются. `translate('12345', '143', 'ax') → a2x5`
unistr ( text ) → text
	Определяет управляющие символы Unicode в аргументе. Символы Unicode можно задать в виде \XXXX (4 шестнадцатеричные цифры), \+XXXXXX (6 шестнадцатеричных цифр), \uXXXX (4 шестнадцатеричные цифры) или \UXXXXXXXX (8 шестнадцатеричных цифр). Чтобы указать обратный слэш, напишите два обратных слэша. Все остальные символы берутся буквально. Если кодировка сервера не UTF-8, символ с кодом Unicode, заданный одной из этих управляющих последовательностей, преобразуется в фактическую кодировку сервера; если это невозможно, выдается ошибка. Эта функция предоставляет (нестандартную) альтернативу строковым константам с управляющими последовательностями Unicode (см. подраздел Строковые константы с экранированием Unicode). `unistr('d\0061t\+000061') → data` `unistr('d\u0061t\U00000061') → data`

Функции 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. См. пример Заключение в кавычки значений в динамических запросах.