| Документация по PostgreSQL 9.1.1 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 9. Функции и операторы | Fast Forward | Next |
Данный раздел описывает функции и операторы для анализа и манипуляций со строковыми значениями. Строки в данном контексте включают значения типов character, character varying и text. За исключением особо оговариваемых случаев, все функции перечисленные ниже работают со всеми этими типами данных, но при использовании типа character, из-за автоматического добавления пробелов, их поведение потенциально может несколько отличаться. Некоторые функции также существуют для типов данных битовых строк.
SQL определяет некоторые строковые функции, которые для разделения аргументов используют ключевые слова, а не запятые. Подробности см. в Table 9-5. PostgreSQL также предоставляет версии данных функций, которые используют регулярный синтаксис вызова функции (см. Table 9-6).
Note: До версии PostgreSQL 8.3, данные функции также должны были молча принимать значения некоторых нестроковых типов данных, таким образом предоставляя неявное приведение этих типов к типу text. Такие приведения были устранены, потому что часто они приводили к неожиданным сюрпризам. Однако, оператор соединения строк (||) по прежнему принимает нестроковые входные значения, если хотя бы одно из значений является строковым, как показано в Table 9-5. Для других случаев, вставляйте явное приведение к типу text, если вам нужно, чтобы было как раньше.
Table 9-5. SQL строковые функции и операторы
| Функция | Возвращает тип | Описание | Пример | Результат |
|---|---|---|---|---|
| строка || строка | text | Соединение строк (конкатенация) | 'Post' || 'greSQL' | PostgreSQL |
| строка || не-строка или не-строка || строка | text | Соединение (конкатенация) с одним нестроковым параметром | 'Value: ' || 42 | Value: 42 |
bit_length(строка)
| int | Количество бит в строке | bit_length('jose') | 32 |
char_length(string) or character_length(string)
| int | Количество символов в строке | char_length('jose') | 4 |
lower(string)
| text | Преобразует строку в нижний регистр | lower('TOM') | tom |
octet_length(string)
| int | Количество байт в строке | octet_length('jose') | 4 |
overlay(string placing string from int [for int])
| text | Заменяет подстроку | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
position(substring in string)
| int | Позиция заданной подстроки в заданной строке | position('om' in 'Thomas') | 3 |
substring(string [from int] [for int])
| text | Извлекает подстроку из заданной строки | substring('Thomas' from 2 for 3) | hom |
substring(string from pattern) | text | Извлекает подстроку, совпадающую с регулярным выражением (шаблоном) POSIX. Подробности о совпадении шаблонов см. в Section 9.7. | substring('Thomas' from '...$') | mas |
substring(string from pattern for escape) | text | Извлекает подстроку, совпадающую с регулярным выражением (шаблоном) SQL. Подробности о совпадении шаблонов см. в Section 9.7. | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
trim([leading | trailing | both]
[символы] from
string)
| text | Удаляет наиболее длинную подстроку, содержащую только заданные символы (по умолчанию пробелы) из start/end/both (начала/конца/с обеих сторон) заданной строки | trim(both 'x' from 'xTomxx') | Tom |
upper(string)
| text | Преобразует строку в вверхний регистр | upper('tom') | TOM |
Также есть дополнительные функции для манипуляций над строками, которые перечислены в Table 9-6. Некоторые из них используются внутри СУБД для реализации функций работы со строками, описанных стандартом SQL и перечисленых в Table 9-5.
Table 9-6. Другие строковые функции
| Функция | Возвращаемый тип | Описание | Пример | Результат |
|---|---|---|---|---|
ascii(string)
| int | ASCII код первого символа заданной строки. Для UTF8 возвращает код Unicode данного символа. Для других многобайтовых кодировок, аргумент должен быть ASCII символом. | ascii('x') | 120 |
btrim(string text
[, characters text])
| text | Удаляет наиболее длинную подстроку, содержащую только заданные символы (по умолчанию, пробел) из начала и конца заданной строки | btrim('xyxtrimyyx', 'xy') | trim |
chr(int)
| text | Символ с указанным кодом. Для UTF8 аргумент трактуется как код Unicode. Для других многобайтовых кодировок аргумент должен соответствовать какому-либо ASCII символу. Символ NULL (0) не разрешается, потому что текстовые типы данных не могут хранить такие символы. | chr(65) | A |
concat(str "any"
[, str "any" [, ...] ])
| text | Concatenate all arguments. NULL arguments are ignored. | concat('abcde', 2, NULL, 22) | abcde222 |
concat_ws(sep text,
str "any"
[, str "any" [, ...] ])
| text | Concatenate all but first arguments with separators. The first parameter is used as a separator. NULL arguments are ignored. | concat_ws(',', 'abcde', 2, NULL, 22) | abcde,2,22 |
convert(string bytea,
src_encoding name,
dest_encoding name)
| bytea | Преобразует строку в кодировку, заданную аргументом dest_encoding. Исходная кодировка задаётся аргументом src_encoding. Аргумент строка должен быть в исходной кодировке. Преобразование может быть задано с помощью CREATE CONVERSION. Также есть несколько предварительно определённых преобразований. См. доступные преобразования в Table 9-7. | convert('text_in_utf8', 'UTF8', 'LATIN1') | text_in_utf8, представленный в кодировке Latin-1 (ISO 8859-1) |
convert_from(string bytea,
src_encoding name)
| text | Преобразует строку в кодировку СУБД. Исходная кодировка задаётся аргументом src_encoding. Заданная строка должна быть и исходной кодировке. | convert_from('text_in_utf8', 'UTF8') | text_in_utf8, представленный в текущей кодировке СУБД |
convert_to(string text,
dest_encoding name)
| bytea | Преобразует строку в кодировку dest_encoding. | convert_to('some text', 'UTF8') | some text, представленный в кодировке UTF8 |
decode(string text,
format text)
| bytea | Декодирует бинарные данных из текстового представления в string.
Опции для format такие же как в encode.
| decode('MTIzAAE=', 'base64') | \x3132330001 |
encode(data bytea,
format text)
| text | Кодирует бинарные данные в текстовое представление. Поддерживаются такие форматы как: base64, hex, escape. Escape просто выводит нулевые байты типа \000 и двойную обратную косую черту. | encode(E'123\\000\\001', 'base64') | MTIzAAE= |
format(formatstr text
[, str "any" [, ...] ])
| text | Format a string. This function is similar to the C function
sprintf; but only the following conversion specifications
are recognized: %s interpolates the corresponding
argument as a string; %I escapes its argument as
an SQL identifier; %L escapes its argument as an
SQL literal; %% outputs a literal %.
A conversion can reference an explicit parameter position by preceding
the conversion specifier with n$, where
n is the argument position.
See also Example 39-1.
| format('Hello %s, %1$s', 'World') | Hello World, World |
initcap(string)
| text | Преобразует первую букву каждого слова в верхний регистр и остаток слова в нижний. Слова являются алфавитно-цифровыми последовательностями, разделенными не алфавитно-цифровыми символами. | initcap('hi THOMAS') | Hi Thomas |
left(str text,
n int)
| text | Return first n characters in the string. When n is negative, return all but last |n| characters. | left('abcde', 2) | ab |
length(string)
| int | Количество символов в заданной строке | length('jose') | 4 |
length(stringbytea,
encoding name ) | int | Количество символов в заданной строке в указанной кодировке. Заданная строка должна быть в указанной кодировке. | length('jose', 'UTF8') | 4 |
lpad(string text,
длина int
[, fill text])
| text | Заполняет заданную строку до указанной длины символами, заданными параметром fill (по умолчанию, пробел). Если строка уже длиннее, чем длина, то она усекается (справа). | lpad('hi', 5, 'xy') | xyxhi |
ltrim(string text
[, characters text])
| text | Удаляет наиболее длинную подстроку, содержащую только заданные символы (по умолчанию, пробел) из начала заданной строки | ltrim('zzzytrim', 'xyz') | trim |
md5(string)
| text | Вычисляет MD5 хэш заданной строки, возвращая резульат в шестнадцетеричном виде | md5('abc') | 900150983cd24fb0 d6963f7d28e17f72 |
pg_client_encoding()
| name | Текущая кодировка клиента | pg_client_encoding() | SQL_ASCII |
quote_ident(string text)
| text | Возвращает заданную строку, заключённую в кавычки должным образом так, чтобы её было можно использовать как идентификатор в операторах SQL, работающих со строками. Кавычки добавляются только если они необходимы (например, если строка содержит символы недопустимые в идентификаторах без кавычек или регистро-зависмые символы). Кавычки внутри строки соответственно дублируются. См. также Example 39-1. | quote_ident('Foo bar') | "Foo bar" |
quote_literal(string text)
| text | Возвращает заданную строку, заключённую в кавычки должным образом так,
чтобы тобы её было можно использовать как строковый литерал в операторах
SQL, работающих со строками. Одиночные кавычки
и символы обратная косая черта внутри строки соответствующим образом
дублируются. Обратите внимание, что функция quote_literal
возвращает NULL при указании NULL в качестве параметра; если аргумент
может быть NULL, часто более подходящей будет функция
quote_nullable. См. также
Example 39-1.
| quote_literal(E'O\'Reilly') | 'O''Reilly' |
quote_literal(value anyelement) | text | Принудительно приводит заданное значение к текстовому типу и затем заключает его в кавычки как литерал. Одиночные кавычки и символы обратная косая черта внутри строки соответственно дублируются. | quote_literal(42.5) | '42.5' |
quote_nullable(string text)
| text | Возвращает заданную строку, заключённую в кавычки должным образом так, чтобы тобы её было можно использовать как строковый литерал в операторах SQL, работающих со строками; или если аргумент является NULL, возвращает return NULL. Одиночные кавычки и символы обратная косая черта внутри строки соответственно дублируются. См. также Example 39-1. | quote_nullable(NULL) | NULL |
quote_nullable(value anyelement) | text | Принудительно приводит указанное значение к текстовому типу и заключает его в кавычки как литерал; или если аргумент это NULL, возвращает NULL. Одиночные кавычки и символы обратная косая черта внутри строки соответственно дублируются. | quote_nullable(42.5) | '42.5' |
regexp_matches(string text, pattern text [, flags text])
| setof text[] | Возвращает все подстроки, полученные в результате совпадения заданной строки с регулярным выражением (шаблоном) POSIX Подробности см. в Section 9.7.3. | regexp_matches('foobarbequebaz', '(bar)(beque)') | {bar,beque} |
regexp_replace(string text, pattern text, replacement text [, flags text])
| text | Заменяет подстроки, совпавшие с регулярным выражением (шаблоном) POSIX в заданной строке. Подробности см. в Section 9.7.3. | regexp_replace('Thomas', '.[mN]a.', 'M') | ThM |
regexp_split_to_array(string text, pattern text [, flags text ])
| text[] | Разбивает заданную строку, используя регулярное выражение (шаблон) POSIX как разделитель. Подробности см. в Section 9.7.3. | regexp_split_to_array('hello world', E'\\s+') | {hello,world} |
regexp_split_to_table(string text, pattern text [, flags text])
| setof text | Разбивает заданную строку, используя регулярное выражение (шаблон) POSIX как разделитель. Подробности см. в Section 9.7.3. | regexp_split_to_table('hello world', E'\\s+') | hello world (2 rows) |
repeat(string text, number int)
| text | Повторяет заданную строку заданное количество раз | repeat('Pg', 4) | PgPgPgPg |
replace(string text,
from text,
to text)
| text | Заменяет все вхождения в заданной строке подстроки from на подстроку to | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
reverse(str)
| text | Return reversed string. | reverse('abcde') | edcba |
right(str text,
n int)
| text | Return last n characters in the string. When n is negative, return all but first |n| characters. | right('abcde', 2) | de |
rpad(string text,
длина int
[, fill text])
| text | Заполняет заданную строку на заданную длину, путём добавления символов, заданных в fill (по умолчанию, пробел). Если заданная строка уже длиннее, чем заданная длина, то она будет усечена. | rpad('hi', 5, 'xy') | hixyx |
rtrim(string text
[, characters text])
| text | Удаляет наиболее длинную подстроку, содержащую заданные символы (по умолчанию, пробел) из конца заданной строки | rtrim('trimxxxx', 'x') | trim |
split_part(string text,
delimiter text,
field int)
| text | Разбивает заданную строку, используя заданный разделитель и возвращает указанное поле (нумерация начинается с единицы) | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
strpos(string, substring)
| int | Положение заданной подстроки (тоже самое, что и функция position(подстрока in строка), но обратите внимание на обратный порядок аргументов) | strpos('high', 'ig') | 2 |
substr(string, from [, count])
| text | Извлекает подстроку (также как функция substring(строка from from for count)) | substr('alphabet', 3, 2) | ph |
to_ascii(string text
[, encoding text])
| text | Преобразует заданную строку в ASCII из другой кодировки (поддерживаются только преобразования из кодировок LATIN1, LATIN2, LATIN9 и WIN1250) | to_ascii('Karel') | Karel |
to_hex(number int
or bigint)
| text | Преобразует заданное число в его шестнадцетеричный эквивалент | to_hex(2147483647) | 7fffffff |
translate(string text,
from text,
to text)
| text | Любой символ из заданной строки, который совпадает с каким-либо символом в наборе символов from, заменяется на соответствующий символ в наборе символов to. Если параметр from длиннее чем to, дополнительные символы в from удаляются. | translate('12345', '143', 'ax') | a2x5 |
См. также агрегатную функцию string_agg в
Section 9.18.
Table 9-7. Встроенные преобразования
| Имя преобразования [a] | Исходная кодировка | Итоговая кодировка |
|---|---|---|
| ascii_to_mic | SQL_ASCII | MULE_INTERNAL |
| ascii_to_utf8 | SQL_ASCII | UTF8 |
| big5_to_euc_tw | BIG5 | EUC_TW |
| big5_to_mic | BIG5 | MULE_INTERNAL |
| big5_to_utf8 | BIG5 | UTF8 |
| euc_cn_to_mic | EUC_CN | MULE_INTERNAL |
| euc_cn_to_utf8 | EUC_CN | UTF8 |
| euc_jp_to_mic | EUC_JP | MULE_INTERNAL |
| euc_jp_to_sjis | EUC_JP | SJIS |
| euc_jp_to_utf8 | EUC_JP | UTF8 |
| euc_kr_to_mic | EUC_KR | MULE_INTERNAL |
| euc_kr_to_utf8 | EUC_KR | UTF8 |
| euc_tw_to_big5 | EUC_TW | BIG5 |
| euc_tw_to_mic | EUC_TW | MULE_INTERNAL |
| euc_tw_to_utf8 | EUC_TW | UTF8 |
| gb18030_to_utf8 | GB18030 | UTF8 |
| gbk_to_utf8 | GBK | UTF8 |
| iso_8859_10_to_utf8 | LATIN6 | UTF8 |
| iso_8859_13_to_utf8 | LATIN7 | UTF8 |
| iso_8859_14_to_utf8 | LATIN8 | UTF8 |
| iso_8859_15_to_utf8 | LATIN9 | UTF8 |
| iso_8859_16_to_utf8 | LATIN10 | UTF8 |
| iso_8859_1_to_mic | LATIN1 | MULE_INTERNAL |
| iso_8859_1_to_utf8 | LATIN1 | UTF8 |
| iso_8859_2_to_mic | LATIN2 | MULE_INTERNAL |
| iso_8859_2_to_utf8 | LATIN2 | UTF8 |
| iso_8859_2_to_windows_1250 | LATIN2 | WIN1250 |
| iso_8859_3_to_mic | LATIN3 | MULE_INTERNAL |
| iso_8859_3_to_utf8 | LATIN3 | UTF8 |
| iso_8859_4_to_mic | LATIN4 | MULE_INTERNAL |
| iso_8859_4_to_utf8 | LATIN4 | UTF8 |
| iso_8859_5_to_koi8_r | ISO_8859_5 | KOI8R |
| iso_8859_5_to_mic | ISO_8859_5 | MULE_INTERNAL |
| iso_8859_5_to_utf8 | ISO_8859_5 | UTF8 |
| iso_8859_5_to_windows_1251 | ISO_8859_5 | WIN1251 |
| iso_8859_5_to_windows_866 | ISO_8859_5 | WIN866 |
| iso_8859_6_to_utf8 | ISO_8859_6 | UTF8 |
| iso_8859_7_to_utf8 | ISO_8859_7 | UTF8 |
| iso_8859_8_to_utf8 | ISO_8859_8 | UTF8 |
| iso_8859_9_to_utf8 | LATIN5 | UTF8 |
| johab_to_utf8 | JOHAB | UTF8 |
| koi8_r_to_iso_8859_5 | KOI8R | ISO_8859_5 |
| koi8_r_to_mic | KOI8R | MULE_INTERNAL |
| koi8_r_to_utf8 | KOI8R | UTF8 |
| koi8_r_to_windows_1251 | KOI8R | WIN1251 |
| koi8_r_to_windows_866 | KOI8R | WIN866 |
| koi8_u_to_utf8 | KOI8U | UTF8 |
| mic_to_ascii | MULE_INTERNAL | SQL_ASCII |
| mic_to_big5 | MULE_INTERNAL | BIG5 |
| mic_to_euc_cn | MULE_INTERNAL | EUC_CN |
| mic_to_euc_jp | MULE_INTERNAL | EUC_JP |
| mic_to_euc_kr | MULE_INTERNAL | EUC_KR |
| mic_to_euc_tw | MULE_INTERNAL | EUC_TW |
| mic_to_iso_8859_1 | MULE_INTERNAL | LATIN1 |
| mic_to_iso_8859_2 | MULE_INTERNAL | LATIN2 |
| mic_to_iso_8859_3 | MULE_INTERNAL | LATIN3 |
| mic_to_iso_8859_4 | MULE_INTERNAL | LATIN4 |
| mic_to_iso_8859_5 | MULE_INTERNAL | ISO_8859_5 |
| mic_to_koi8_r | MULE_INTERNAL | KOI8R |
| mic_to_sjis | MULE_INTERNAL | SJIS |
| mic_to_windows_1250 | MULE_INTERNAL | WIN1250 |
| mic_to_windows_1251 | MULE_INTERNAL | WIN1251 |
| mic_to_windows_866 | MULE_INTERNAL | WIN866 |
| sjis_to_euc_jp | SJIS | EUC_JP |
| sjis_to_mic | SJIS | MULE_INTERNAL |
| sjis_to_utf8 | SJIS | UTF8 |
| tcvn_to_utf8 | WIN1258 | UTF8 |
| uhc_to_utf8 | UHC | UTF8 |
| utf8_to_ascii | UTF8 | SQL_ASCII |
| utf8_to_big5 | UTF8 | BIG5 |
| utf8_to_euc_cn | UTF8 | EUC_CN |
| utf8_to_euc_jp | UTF8 | EUC_JP |
| utf8_to_euc_kr | UTF8 | EUC_KR |
| utf8_to_euc_tw | UTF8 | EUC_TW |
| utf8_to_gb18030 | UTF8 | GB18030 |
| utf8_to_gbk | UTF8 | GBK |
| utf8_to_iso_8859_1 | UTF8 | LATIN1 |
| utf8_to_iso_8859_10 | UTF8 | LATIN6 |
| utf8_to_iso_8859_13 | UTF8 | LATIN7 |
| utf8_to_iso_8859_14 | UTF8 | LATIN8 |
| utf8_to_iso_8859_15 | UTF8 | LATIN9 |
| utf8_to_iso_8859_16 | UTF8 | LATIN10 |
| utf8_to_iso_8859_2 | UTF8 | LATIN2 |
| utf8_to_iso_8859_3 | UTF8 | LATIN3 |
| utf8_to_iso_8859_4 | UTF8 | LATIN4 |
| utf8_to_iso_8859_5 | UTF8 | ISO_8859_5 |
| utf8_to_iso_8859_6 | UTF8 | ISO_8859_6 |
| utf8_to_iso_8859_7 | UTF8 | ISO_8859_7 |
| utf8_to_iso_8859_8 | UTF8 | ISO_8859_8 |
| utf8_to_iso_8859_9 | UTF8 | LATIN5 |
| utf8_to_johab | UTF8 | JOHAB |
| utf8_to_koi8_r | UTF8 | KOI8R |
| utf8_to_koi8_u | UTF8 | KOI8U |
| utf8_to_sjis | UTF8 | SJIS |
| utf8_to_tcvn | UTF8 | WIN1258 |
| utf8_to_uhc | UTF8 | UHC |
| utf8_to_windows_1250 | UTF8 | WIN1250 |
| utf8_to_windows_1251 | UTF8 | WIN1251 |
| utf8_to_windows_1252 | UTF8 | WIN1252 |
| utf8_to_windows_1253 | UTF8 | WIN1253 |
| utf8_to_windows_1254 | UTF8 | WIN1254 |
| utf8_to_windows_1255 | UTF8 | WIN1255 |
| utf8_to_windows_1256 | UTF8 | WIN1256 |
| utf8_to_windows_1257 | UTF8 | WIN1257 |
| utf8_to_windows_866 | UTF8 | WIN866 |
| utf8_to_windows_874 | UTF8 | WIN874 |
| windows_1250_to_iso_8859_2 | WIN1250 | LATIN2 |
| windows_1250_to_mic | WIN1250 | MULE_INTERNAL |
| windows_1250_to_utf8 | WIN1250 | UTF8 |
| windows_1251_to_iso_8859_5 | WIN1251 | ISO_8859_5 |
| windows_1251_to_koi8_r | WIN1251 | KOI8R |
| windows_1251_to_mic | WIN1251 | MULE_INTERNAL |
| windows_1251_to_utf8 | WIN1251 | UTF8 |
| windows_1251_to_windows_866 | WIN1251 | WIN866 |
| windows_1252_to_utf8 | WIN1252 | UTF8 |
| windows_1256_to_utf8 | WIN1256 | UTF8 |
| windows_866_to_iso_8859_5 | WIN866 | ISO_8859_5 |
| windows_866_to_koi8_r | WIN866 | KOI8R |
| windows_866_to_mic | WIN866 | MULE_INTERNAL |
| windows_866_to_utf8 | WIN866 | UTF8 |
| windows_866_to_windows_1251 | WIN866 | WIN |
| windows_874_to_utf8 | WIN874 | UTF8 |
| euc_jis_2004_to_utf8 | EUC_JIS_2004 | UTF8 |
| ut8_to_euc_jis_2004 | UTF8 | EUC_JIS_2004 |
| shift_jis_2004_to_utf8 | SHIFT_JIS_2004 | UTF8 |
| ut8_to_shift_jis_2004 | UTF8 | SHIFT_JIS_2004 |
| euc_jis_2004_to_shift_jis_2004 | EUC_JIS_2004 | SHIFT_JIS_2004 |
| shift_jis_2004_to_euc_jis_2004 | SHIFT_JIS_2004 | EUC_JIS_2004 |
| Notes: a. Имена преобразований следуют стандартой схеме именования: официальное имя исходной кодировки со всеми неалфавитно-цифровыми символами, заменяемыми подчёркиванием, за которым следует _to_, за которым следует формируемое точно по таким же правилами имя кодировки, в которую осуществляется преобразование. Таким образом, имена могут отклоняться от общепринятых имён кодировок. | ||