9.4. Функции и операторы строк#

9.4. Функции и операторы строк

9.4. Функции и операторы строк #

Этот раздел описывает функции и операторы для изучения и манипулирования строковыми значениями. Строки в этом контексте включают значения типов character, character varying, и text. За исключением указанных случаев, эти функции и операторы объявлены для принятия и возврата типа text. Они могут принимать аргументы типа character varying. Значения типа character будут преобразованы в text перед применением функции или оператора, что приведет к удалению любых конечных пробелов в значении типа character.

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

Примечание

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

Таблица 9.9. Функции и операторы строк SQL

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

Описание

Пример(ы)

text || texttext

Сцепляет две строки.

'Post' || 'greSQL'PostgreSQL

text || anynonarraytext

anynonarray || texttext

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

'Value: ' || 42Value: 42

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

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

btrim('xyxtrimyyx', 'xyz')trim

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

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

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

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

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

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

ltrim('zzzytest', 'xyz')test

normalize ( text [, форма ] ) → 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, или ноль, если она отсутствует.

position('om' in 'Thomas')3

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

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

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

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

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

rtrim('testxxzx', 'xyz')test

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 SIMILAR pattern text ESCAPE escape text ) → text

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

Извлекает первую подстроку, соответствующую регулярному выражению SQL; см. Раздел 9.7.2. Первая форма была определена с SQL:2003; вторая форма была только в SQL:1999 и должна считаться устаревшей.

substring('Thomas' similar '%#"o_a#"_' escape '#')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


Дополнительные функции и операторы для работы со строками доступны и перечислены в Таблица 9.10. (Некоторые из них используются внутренне для реализации стандартных строковых функций SQL, перечисленных в Таблица 9.9). Также существуют операторы сопоставления с образцом, которые описаны в Раздел 9.7, и операторы для полнотекстового поиска, которые описаны в Глава 12.

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

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

Описание

Пример(ы)

text ^@ textboolean

Возвращает true, если первая строка начинается со второй строки (эквивалентно функции starts_with()).

'alphabet' ^@ 'alph't

ascii ( text ) → integer

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

ascii('x')120

chr ( integer ) → text

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

chr(65)A

concat ( val1 "любой" [, val2 "любой" [, ...] ] ) → text

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

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

concat_ws ( sep text, val1 "любой" [, val2 "любой" [, ...] ] ) → 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

left ( string text, n integer ) → text

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

left('abcde', 2)ab

length ( text ) → integer

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

length('jose')4

md5 ( text ) → text

Вычисляет hash 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; если аргумент может быть 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_count ( string text, pattern text [, start integer [, flags text ] ] ) → integer

Возвращает количество совпадений с шаблоном POSIX регулярного выражения pattern в string; см. Раздел 9.7.3.

regexp_count('123456789012', '\d\d\d', 2)3

regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer

Возвращает позицию внутри string, где находится N-ое совпадение с POSIX-регулярным выражением pattern, или ноль, если такого совпадения нет; см. Раздел 9.7.3.

regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i')3

regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i', 2)5

regexp_like ( string text, pattern text [, флаги text ] ) → boolean

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

regexp_like('Hello World', 'world$', 'i')t

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

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

regexp_match('foobarbequebaz', '(bar)(beque)'){bar,beque}

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

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

regexp_matches('foobarbequebaz', 'ba.', 'g')

 {bar}
 {baz}

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

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

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

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

Заменяет подстроку, которая является N-м совпадением с POSIX регулярным выражением pattern, или все такие совпадения, если N равно нулю; см. Раздел 9.7.3.

regexp_replace('Thomas', '.', 'X', 3, 2)ThoXas

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

regexp_substr ( string text, pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ] ) → text

Возвращает подстроку внутри string, которая соответствует N-му вхождению POSIX регулярного выражения pattern, или NULL, если такого совпадения нет; см. Раздел 9.7.3.

regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i')CDEF

regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i', 2)EF

repeat ( string text, number integer ) → text

Повторяет string указанное количество number.

repeat('Pg', 4)PgPgPgPg

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

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

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

reverse ( text ) → text

Обращает порядок символов в строке.

reverse('abcde')edcba

right ( string text, n integer ) → text

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

right('abcde', 2)de

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

Разделяет string на части по вхождениям delimiter и возвращает n-е поле (считая с единицы), или, когда n отрицательное, возвращает |n|-е поле с конца.

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

split_part('abc,def,ghi,jkl', ',', -2)ghi

starts_with ( string text, prefix text ) → boolean

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

starts_with('alphabet', 'alph')t

string_to_array ( string text, delimiter text [, null_string text ] ) → text[]

Сплитит строку string на элементы по вхождениям delimiter и формирует полученные поля в массив text. Если delimiter равен NULL, каждый символ в string станет отдельным элементом массива. Если delimiter является пустой строкой, то string рассматривается как одно поле. Если предоставлено null_string и оно не равно NULL, поля, соответствующие этой строке, заменяются на NULL. См. также array_to_string.

string_to_array('xx~~yy~~zz', '~~', 'yy'){xx,NULL,zz}

string_to_table ( string text, delimiter text [, null_string text ] ) → setof text

Разделяет string на поля по вхождениям delimiter и возвращает результат в виде набора строк типа text. Если delimiter равен NULL, каждый символ в string будет отдельной строкой в результате. Если delimiter является пустой строкой, то string рассматривается как одно поле. Если указана null_string и она не равна NULL, поля, совпадающие с этой строкой, заменяются на NULL.

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

 xx
 NULL
 zz

strpos ( string text, substring text ) → integer

Возвращает первый индекс начала указанной подстроки substring внутри string, или ноль, если она отсутствует. (То же самое, что и 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

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

unistr ( text ) → text

Оцените экранированные символы Юникода в аргументе. Символы Юникода можно указать как \XXXX (4 шестнадцатеричных цифры), \+XXXXXX (6 шестнадцатеричных цифр), \uXXXX (4 шестнадцатеричных цифры) или \UXXXXXXXX (8 шестнадцатеричных цифр). Чтобы указать обратную косую черту, напишите две обратные косые черты. Все остальные символы принимаются буквально.

Если кодировка сервера не UTF-8, кодовая точка Unicode, идентифицируемая одной из этих последовательностей экранирования, преобразуется в фактическую кодировку сервера; если это невозможно, выдается ошибка.

Эта функция предоставляет (нестандартную) альтернативу строковым константам с использованием экранирующих символов Unicode (см. Раздел 4.1.2.3).

unistr('d\0061t\+000061')data

unistr('d\u0061t\U00000061')data


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

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

9.4.1. format #

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

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

formatstr - это строка формата, которая определяет, как должен быть отформатирован результат. Текст в строке формата копируется непосредственно в результат, за исключением случаев, когда используются спецификаторы формата. Спецификаторы формата действуют как заполнители в строке, определяя, как следующие аргументы функции должны быть отформатированы и вставлены в результат. Каждый аргумент formatarg преобразуется в текст в соответствии с обычными правилами вывода для его типа данных, а затем форматируется и вставляется в строку результата в соответствии с спецификатором(ами) формата.

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

%[position][flags][width]type

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

position (optional)

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

flags (optional)

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

width (optional)

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

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

type (required)

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

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

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

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