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

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

В SQL определены несколько строковых функций, в которых аргументы разделяются не запятыми, а ключевыми словами. Они перечислены в Таблице 9.9. Postgres Pro также предоставляет варианты этих функций с синтаксисом, обычным для функций (см. Таблицу 9.10).

Примечание

До версии 8.3 в PostgreSQL эти функции также прозрачно принимали значения некоторых не строковых типов, неявно приводя эти значения к типу text. Сейчас такие приведения исключены, так как они часто приводили к неожиданным результатам. Однако оператор конкатенации строк (||) по-прежнему принимает не только строковые данные, если хотя бы один аргумент имеет строковый тип, как показано в Таблице 9.9. Во всех остальных случаях для повторения предыдущего поведения потребуется добавить явное преобразование в text.

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

Функция/оператор

Описание

Пример(ы)

text || texttext

Соединяет две строки.

'Post' || 'greSQL'PostgreSQL

text || anynonarraytext

anynonarray || texttext

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

'Value: ' || 42Value: 42

text IS [NOT] [form] NORMALIZEDboolean

Проверяет, соответствует ли строка определённой форме нормализации Юникода. Форма указывается в необязательном ключевом слове form: NFC (по умолчанию), NFD, NFKC или NFKD. Это выражение можно использовать, только если кодировка сервера — UTF8. Заметьте, что проверить нормализацию с помощью этого выражения, как правило, будет быстрее, чем нормализовать уже, возможно, нормализованные строки.

U&'\0061\0308bc' IS NFD NORMALIZEDt

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 [, form] ) → text

Переводит строку в заданную форму нормализации Unicode. Форма указывается в необязательном ключевом слове form: 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 ( string text PLACING newsubstring text FROM start integer [FOR count integer] ) → text

Заменяет подстроку в string, начиная с символа с номером start, длиной count символов, на подстроку newsubstring. В отсутствие параметра count количество заменяемых символов определяется длиной newsubstring.

overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas

position ( substring text IN string text ) → integer

Возвращает начальную позицию первого вхождения substring в строке string либо 0, если такого вхождения нет.

position('om' in 'Thomas')3

substring ( string text [FROM start integer] [FOR count integer] ) → text

Извлекает из string подстроку, начиная с позиции start (если она указана), длиной до count символов (если она указана). Параметры start и count могут опускаться, но не оба сразу.

substring('Thomas' from 2 for 3)hom

substring('Thomas' from 3)omas

substring('Thomas' for 2)Th

substring ( string text FROM pattern text ) → text

Извлекает первую подстроку, соответствующую регулярному выражению в стиле POSIX; см. Подраздел 9.7.3.

substring('Thomas' from '...$')mas

substring ( string text FROM pattern text FOR escape text ) → text

Извлекает первую подстроку, соответствующую регулярному выражению в стиле SQL; см. Подраздел 9.7.2.

substring('Thomas' from '%#"o_a#"_' for '#')oma

trim ( [LEADING | TRAILING | BOTH] [characters text] FROM string text ) → text

Удаляет наибольшую подстроку, содержащую только символы characters (по умолчанию пробелы), с начала, с конца или с обеих сторон (BOTH, по умолчанию) строки string.

trim(both 'xyz' from 'yxTomxx')Tom

trim ( [LEADING | TRAILING | BOTH] [FROM] string text [, characters text] ) → text

Это нестандартный синтаксис вызова trim().

trim(both from 'yxTomxx', 'xyz')Tom

upper ( text ) → text

Переводит символы строки в верхний регистр, в соответствии с правилами локали базы данных.

upper('tom')TOM


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

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

Функция

Описание

Пример(ы)

ascii ( text ) → integer

Возвращает числовой код первого символа аргумента. Для UTF8 возвращает код символа в Unicode. Для других многобайтных кодировок аргумент должен быть ASCII-символом.

ascii('x')120

btrim ( string text [, characters text] ) → text

Удаляет наибольшую подстроку, содержащую только символы characters (по умолчанию пробел), с начала и с конца строки string.

btrim('xyxtrimyyx', 'xyz')trim

chr ( integer ) → text

Возвращает символ с данным кодом. Для UTF8 аргумент воспринимается как код символа Unicode, а для других кодировок он должен указывать на ASCII-символ. Эта функция не может выдать chr(0), так как данный символ нельзя сохранить в текстовых типах данных.

chr(65)A

concat ( val1 "any" [, val2 "any" [, ...] ] ) → text

Соединяет текстовые представления всех аргументов, игнорируя NULL.

concat('abcde', 2, NULL, 22)abcde222

concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text

Соединяет вместе все аргументы, кроме первого, через разделитель. Разделитель задаётся в первом аргументе и должен быть отличен от NULL. В других аргументах значение NULL игнорируется.

concat_ws(',', 'abcde', 2, NULL, 22)abcde,2,22

format ( formatstr text [, formatarg "any" [, ...] ] ) → text

Форматирует аргументы в соответствии со строкой формата; см. Подраздел 9.4.1. Эта функция работает подобно sprintf в языке C.

format('Hello %s, %1$s', 'World')Hello World, World

initcap ( text ) → text

Переводит первую букву каждого слова в строке в верхний регистр, а остальные — в нижний. Словами считаются последовательности алфавитно-цифровых символов, разделённые любыми другими символами.

initcap('hi THOMAS')Hi Thomas

left ( string text, n integer ) → text

Возвращает первые n символов в строке. Когда n меньше нуля, возвращаются все символы слева, кроме последних |n|.

left('abcde', 2)ab

length ( text ) → integer

Возвращает число символов в строке.

length('jose')4

lpad ( string text, length integer [, fill text] ) → text

Дополняет строку string слева до длины length символами fill (по умолчанию пробелами). Если длина строки уже больше заданной, она обрезается справа.

lpad('hi', 5, 'xy')xyxhi

ltrim ( string text [, characters text] ) → text

Удаляет наибольшую подстроку, содержащую только символы characters (по умолчанию пробелы), с начала строки string.

ltrim('zzzytest', 'xyz')test

md5 ( text ) → text

Вычисляет MD5-хеш аргумента и выдаёт результат в шестнадцатеричном виде.

md5('abc')900150983cd24fb0​d6963f7d28e17f72

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[]

Раскладывает полный идентификатор, задаваемый параметром qualified_identifier, на массив идентификаторов, удаляя кавычки, обрамляющие отдельные идентификаторы. По умолчанию лишние символы после последнего идентификатора вызывают ошибку, но если отключить строгий режим (передать во втором параметре false), такие символы игнорируются. (Это поведение полезно для разбора имён таких объектов, как функции.) Заметьте, что эта функция не усекает чрезмерно длинные идентификаторы. Если вы хотите получить усечённые имена, можно привести результат к name[].

parse_ident('"SomeSchema".someTable'){SomeSchema,sometable}

pg_client_encoding ( ) → name

Возвращает имя текущей клиентской кодировки.

pg_client_encoding()UTF8

quote_ident ( text ) → text

Преобразует аргумент в строку, подходящую для использования в качестве идентификатора в SQL-операторе. При необходимости идентификатор заключается в кавычки (например, если он содержит символы, недопустимые в открытом виде, или буквы в разных регистрах). Если переданная строка содержит кавычки, они дублируются. См. также Пример 40.1.

quote_ident('Foo bar')"Foo bar"

quote_literal ( text ) → text

Преобразует аргумент в строку, подходящую для использования в качестве текстовой константы в SQL-операторе. Внутренние символы апостроф и обратная косая черта при этом дублируются. Заметьте, что quote_literal возвращает NULL, когда на вход ей передаётся строка NULL; если же нужно получить представление и такого аргумента, лучше использовать quote_nullable. См. также Пример 40.1.

quote_literal(E'O\'Reilly')'O''Reilly'

quote_literal ( anyelement ) → text

Переводит данное значение в текстовый вид и заключает в апострофы как текстовую строку. Символы апостроф и обратная косая черта при этом дублируются.

quote_literal(42.5)'42.5'

quote_nullable ( text ) → text

Преобразует аргумент в строку, подходящую для использования в качестве текстовой константы в SQL-операторе; при этом для аргумента NULL возвращается строка NULL. Символы апостроф и обратная косая черта дублируются должным образом. См. также Пример 40.1.

quote_nullable(NULL)NULL

quote_nullable ( anyelement ) → text

Переводит данное значение в текстовый вид и заключает в апострофы как текстовую строку, при этом для аргумента NULL возвращается строка NULL. Символы апостроф и обратная косая черта дублируются должным образом.

quote_nullable(42.5)'42.5'

regexp_match ( string text, pattern text [, flags text ] ) → text[]

Возвращает подходящие подстроки, полученные из первого вхождения регулярного выражения POSIX в строке string; см. Подраздел 9.7.3.

regexp_match('foobar​bequebaz', '(bar)(beque)'){bar,beque}

regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]

Возвращает подходящие подстроки, полученные из первого вхождения регулярного выражения POSIX в строке string или из нескольких вхождений, если использован флаг g; см. Подраздел 9.7.3.

regexp_matches('foobar​bequebaz', 'ba.', 'g')

 {bar}
 {baz}

regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text

Заменяет подходящие подстроки, полученные из первого вхождения регулярного выражения POSIX или подходящие подстроки из нескольких вхождений, если использован флаг g; см. Подраздел 9.7.3.

regexp_replace('Thomas', '.[mN]a.', 'M')ThM

regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]

Разделяет содержимое string на элементы, используя в качестве разделителя регулярное выражение POSIX; см. Подраздел 9.7.3.

regexp_split_to_array('hello world', '\s+'){hello,world}

regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text

Разделяет содержимое string на элементы, используя в качестве разделителя регулярное выражение POSIX; см. Подраздел 9.7.3.

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

 hello
 world

repeat ( string text, number integer ) → text

Повторяет содержимое string указанное число (number) раз.

repeat('Pg', 4)PgPgPgPg

replace ( string text, from text, to text ) → text

Заменяет все вхождения в string подстроки from подстрокой to.

replace('abcdefabcdef', 'cd', 'XX')abXXefabXXef

reverse ( text ) → text

Переставляет символы в строке в обратном порядке.

reverse('abcde')edcba

right ( string text, n integer ) → text

Возвращает последние n символов в строке. Когда n меньше нуля, возвращаются все символы справа, кроме первых |n|.

right('abcde', 2)de

rpad ( string text, length integer [, fill text] ) → text

Дополняет строку string справа до длины length символами fill (по умолчанию пробелами). Если длина строки уже больше заданной, она обрезается.

rpad('hi', 5, 'xy')hixyx

rtrim ( string text [, characters text] ) → text

Удаляет наибольшую подстроку, содержащую только символы characters (по умолчанию пробелы), с конца строки string.

rtrim('testxxzx', 'xyz')test

split_part ( string text, delimiter text, n integer ) → text

Разделяет строку string по символу delimiter и возвращает элемент по заданному номеру (считая с 1).

split_part('abc~@~def​~@~ghi', '~@~', 2)def

strpos ( string text, substring text ) → integer

Возвращает начальную позицию первого вхождения substring в строке string либо 0, если такого вхождения нет. (Ей подобна функция position(substring in string), но обратите внимание на другой порядок аргументов.)

strpos('high', 'ig')2

substr ( string text, start integer [, count integer] ) → text

Извлекает из string подстроку, начиная с позиции start, длиной до count символов (если это значение указано). (Ей равнозначна функция substring(string from start for count).)

substr('alphabet', 3)phabet

substr('alphabet', 3, 2)ph

starts_with ( string text, prefix text ) → boolean

Возвращает true, если строка string начинается с подстроки prefix.

starts_with('alphabet', 'alph')t

to_ascii ( string text ) → text

to_ascii ( string text, encoding name ) → text

to_ascii ( string text, encoding integer ) → text

Преобразует string в ASCII из другой кодировки, задаваемой по имени или номеру. В отсутствие указания encoding подразумевается кодировка базы данных (и на практике это единственный полезный вариант использования). В основном суть преобразования сводится к отбрасыванию диакритических знаков. Это преобразование поддерживается только для кодировок LATIN1, LATIN2, LATIN9 и WIN1250. (Другое, более гибкое решение реализовано в модуле unaccent).

to_ascii('Karél')Karel

to_hex ( integer ) → text

to_hex ( bigint ) → text

Преобразует число в шестнадцатеричное представление.

to_hex(2147483647)7fffffff

translate ( string text, from text, to text ) → text

Заменяет каждый символ в string, входящий в множество from, на соответствующий символ в множестве to. Если строка from длиннее to, найденные в исходной строке лишние символы from удаляются.

translate('12345', '143', 'ax')a2x5


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

Также обратите внимание на агрегатную функцию string_agg в Разделе 9.21 и функции для преобразования текста в bytea и наоборот в Таблице 9.13.

9.4.1. format

Функция format выдаёт текст, отформатированный в соответствии со строкой формата, подобно функции sprintf в C.

format(formatstr text [, 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. См. Пример 40.1.