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

В этом разделе описываются функции и операторы для проверки и манипулирования строковыми значениями. Строки в этом контексте включают значения типов 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') → 900150983cd24fb0​d6963f7d28e17f72

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