9.4. 字符串函数和操作符 — PostgreSQL 9.2

支持中的版本：当前版本 (18) / 17 / 16 / 15 / 14

开发中的版本： 19 / 20devel

已结束支持的版本： 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1

历史版本。 PostgreSQL 9.2 已结束支持。 2017-11-09. 请参阅当前版本手册.

9.4. 字符串函数和操作符
上一页	上一级	第 9 章函数和操作符	起始页	下一页

9.4. 字符串函数和操作符 #

本节描述用于检查和操作字符串值的函数和操作符。这里的字符串包括 character、character varying 和 text 类型的值。除非另有说明，下面列出的所有函数都适用于这些类型，但使用 character 类型时，应注意自动填充空格可能带来的影响。一些函数也为位串类型提供了原生实现。

SQL定义了一些字符串函数，它们使用关键字，而不是逗号来分隔参数。详情请见表 9.6，PostgreSQL也提供了这些函数使用正常函数调用语法的版本（见表 9.7）。

注意

在 PostgreSQL 8.3 之前，由于某些非字符串数据类型到 text 的隐式强制转换，这些函数也会悄然接受那些类型的值。由于这些强制转换经常产生出人意料的行为，它们已被移除。不过，字符串串接操作符（||）仍接受非字符串输入，只要至少一个输入属于字符串类型，如表 9.6所示。对于其他情况，如果需要重现以前的行为，可添加到 text 的显式强制转换。

表 9.6. SQL字符串函数和操作符

函数	返回类型	描述	示例	结果
`string \|\| string`	`text`	字符串串接	`'Post' \|\| 'greSQL'`	`PostgreSQL`
`string \|\| non-string` 或 `non-string \|\| string`	`text`	一个输入为非字符串的字符串串接	`'Value: ' \|\| 42`	`Value: 42`
`bit_length(string)`	`int`	字符串中的位数	`bit_length('jose')`	`32`
`char_length(string)` 或 `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 正则表达式的子字符串。有关模式匹配的更多信息，参见第 9.7 节。	`substring('Thomas' from '...$')`	`mas`
`substring(string from pattern for escape)`	`text`	提取匹配 SQL 正则表达式的子字符串。有关模式匹配的更多信息，参见第 9.7 节。	`substring('Thomas' from '%#"o_a#"_' for '#')`	`oma`
`trim([leading \| trailing \| both] [characters] from string)`	`text`	从 `string` 的开头、结尾或两端（默认为 `both`）移除仅由 `characters` 中字符（默认为空格）组成的最长字符串	`trim(both 'x' from 'xTomxx')`	`Tom`
`upper(string)`	`text`	将字符串转换为大写	`upper('tom')`	`TOM`

还有其他字符串操作函数，列在表 9.7中。其中一些用于内部实现表 9.6中列出的 SQL 标准字符串函数。

表 9.7. 其他字符串函数

函数	返回类型	描述	示例	结果
`ascii(string)`	`int`	参数首字符的 ASCII 码。对于 UTF8，返回该字符的 Unicode 码点。对于其他多字节编码，参数必须是 ASCII 字符。	`ascii('x')`	`120`
`btrim(string text [, characters text])`	`text`	从 `string` 的开头和结尾移除仅由 `characters` 中字符（默认为空格）组成的最长字符串	`btrim('xyxtrimyyx', 'xy')`	`trim`
`chr(int)`	`text`	指定编码值对应的字符。对于 UTF8，参数被视为 Unicode 码点。对于其他多字节编码，参数必须指定一个 ASCII 字符。不允许 NULL（0）字符，因为文本数据类型无法存储这样的字节。	`chr(65)`	`A`
`concat(str "any" [, str "any" [, ...] ])`	`text`	串接所有参数的文本表示。忽略 NULL 参数。	`concat('abcde', 2, NULL, 22)`	`abcde222`
`concat_ws(sep text, str "any" [, str "any" [, ...] ])`	`text`	使用分隔符串接除第一个参数以外的所有参数。第一个参数用作分隔字符串。忽略 NULL 参数。	`concat_ws(',', 'abcde', 2, NULL, 22)`	`abcde,2,22`
`convert(string bytea, src_encoding name, dest_encoding name)`	`bytea`	将字符串转换为 `dest_encoding`。原编码由 `src_encoding` 指定。`string` 必须是该编码下的有效字符串。可以用 `CREATE CONVERSION` 定义转换。此外，也有一些预定义的转换。可用的转换见表 9.8。	`convert('text_in_utf8', 'UTF8', 'LATIN1')`	以 Latin-1 编码（ISO 8859-1）表示的 `text_in_utf8`
`convert_from(string bytea, src_encoding name)`	`text`	将字符串转换为数据库编码。原编码由 `src_encoding` 指定。`string` 必须是该编码下的有效字符串。	`convert_from('text_in_utf8', 'UTF8')`	以当前数据库编码表示的 `text_in_utf8`
`convert_to(string text, dest_encoding name)`	`bytea`	将字符串转换为 `dest_encoding`。	`convert_to('some text', 'UTF8')`	以 UTF8 编码表示的 `some text`
`decode(string text, format text)`	`bytea`	从 `string` 中的文本表示解码二进制数据。`format` 的选项与 `encode` 相同。	`decode('MTIzAAE=', 'base64')`	`\x3132330001`
`encode(data bytea, format text)`	`text`	将二进制数据编码为文本表示。支持的格式为：`base64`、`hex`、`escape`。`escape` 将零字节和最高位为 1 的字节转换为八进制序列（`\``nnn`），并将反斜杠双写。	`encode(E'123\\000\\001', 'base64')`	`MTIzAAE=`
`format(formatstr text [, formatarg "any" [, ...] ])`	`text`	格式化字符串。此函数类似于 C 函数 `sprintf`，但只识别下列转换说明：`%s`将对应参数作为字符串插入；`%I`将其参数转义为 SQL 标识符；`%L`将其参数转义为 SQL 字面量；`%%`输出一个字面 `%`。转换可以通过在转换说明符前加上`n$`来引用显式的参数位置，其中`n`是参数位置。另见例 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`	返回字符串中的前 `n` 个字符。当 `n` 为负数时，返回除最后 \|`n`\| 个字符以外的所有字符。	`left('abcde', 2)`	`ab`
`length(string)`	`int`	`string` 中的字符数	`length('jose')`	`4`
`length(string bytea, encoding name )`	`int`	采用给定 `encoding` 的 `string` 中的字符数。`string` 必须是该编码下的有效字符串。	`length('jose', 'UTF8')`	`4`
`lpad(string text, length int [, fill text])`	`text`	在 `string` 前面添加字符 `fill`（默认为空格），将其填充到长度 `length`。如果 `string` 已经长于 `length`，则从右侧截断。	`lpad('hi', 5, 'xy')`	`xyxhi`
`ltrim(string text [, characters text])`	`text`	从 `string` 的开头移除仅由 `characters` 中字符（默认为空格）组成的最长字符串	`ltrim('zzzytrim', 'xyz')`	`test`
`md5(string)`	`text`	计算 `string` 的 MD5 hash，并以十六进制返回结果	`md5('abc')`	`900150983cd24fb0 d6963f7d28e17f72`
`pg_client_encoding()`	`name`	当前客户端编码名称	`pg_client_encoding()`	`SQL_ASCII`
`quote_ident(string text)`	`text`	为给定字符串添加适当的引号并返回，使其可用作 SQL 语句字符串中的标识符。仅在必要时添加引号（即字符串包含不能用于标识符的字符，或会被大小写折叠时）。内嵌引号会适当地双写。另见例 39.1。	`quote_ident('Foo bar')`	`"Foo bar"`
`quote_literal(string text)`	`text`	为给定字符串添加适当的引号并返回，使其可用作 SQL 语句字符串中的字符串字面量。内嵌单引号和反斜杠会被适当地双写。注意，`quote_literal` 在输入为 null 时返回 null；如果参数可能为 null，`quote_nullable` 通常更合适。另见例 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，则返回 `NULL`。内嵌单引号和反斜杠会被适当地双写。另见例 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 正则表达式与 `string` 匹配所得到的所有捕获子串。更多信息参见第 9.7.3 节。	`regexp_matches('foobarbequebaz', '(bar)(beque)')`	`{bar,beque}`
`regexp_replace(string text, pattern text, replacement text [, flags text])`	`text`	替换匹配 POSIX 正则表达式的子字符串。更多信息参见第 9.7.3 节。	`regexp_replace('Thomas', '.[mN]a.', 'M')`	`ThM`
`regexp_split_to_array(string text, pattern text [, flags text ])`	`text[]`	使用 POSIX 正则表达式作为分隔符拆分 `string`。更多信息参见第 9.7.3 节。	`regexp_split_to_array('hello world', '\s+')`	`{hello,world}`
`regexp_split_to_table(string text, pattern text [, flags text])`	`setof text`	使用 POSIX 正则表达式作为分隔符拆分 `string`。更多信息参见第 9.7.3 节。	`regexp_split_to_table('hello world', '\s+')`	`hello` `world` （2 行）
`repeat(string text, number int)`	`text`	将 `string` 重复指定的 `number` 次	`repeat('Pg', 4)`	`PgPgPgPg`
`replace(string text, from text, to text)`	`text`	将 `string` 中每次出现的子字符串 `from` 替换为子字符串 `to`	`replace('abcdefabcdef', 'cd', 'XX')`	`abXXefabXXef`
`reverse(str)`	`text`	返回反转后的字符串。	`reverse('abcde')`	`edcba`
`right(str text, n int)`	`text`	返回字符串中的最后 `n` 个字符。当 `n` 为负数时，返回除前 \|`n`\| 个字符以外的所有字符。	`right('abcde', 2)`	`de`
`rpad(string text, length int [, fill text])`	`text`	在 `string` 后面追加字符 `fill`（默认为空格），将其填充到长度 `length`。如果 `string` 已经长于 `length`，则截断它。	`rpad('hi', 5, 'xy')`	`hixyx`
`rtrim(string text [, characters text])`	`text`	从 `string` 的结尾移除仅由 `characters` 中字符（默认为空格）组成的最长字符串	`rtrim('testim', 'xyz')`	`test`
`split_part(string text, delimiter text, field int)`	`text`	按 `delimiter` 拆分 `string`，并返回指定字段（从一开始计数）	`split_part('abc~@~def~@~ghi', '~@~', 2)`	`def`
`strpos(string, substring)`	`int`	指定子字符串的位置（与 `position(substring in string)` 相同，但注意参数顺序相反）	`strpos('high', 'ig')`	`2`
`substr(string, from [, count])`	`text`	提取子字符串（与 `substring(string from from for count)` 相同）	`substr('alphabet', 3, 2)`	`ph`
`to_ascii(string text [, encoding text])`	`text`	将 `string` 从其他编码转换为 ASCII（只支持从 `LATIN1`、`LATIN2`、`LATIN9` 和 `WIN1250` 编码转换）	`to_ascii('Karel')`	`Karel`
`to_hex(number int or bigint)`	`text`	将 `number` 转换为等价的十六进制表示	`to_hex(2147483647)`	`7fffffff`
`translate(string text, from text, to text)`	`text`	将 `string` 中与 `from` 集合中某字符匹配的每个字符替换为 `to` 集合中的对应字符。如果 `from` 比 `to` 长，则移除输入中出现的 `from` 中的多余字符。	`translate('12345', '143', 'ax')`	`a2x5`

另见第 9.20 节中的聚合函数 string_agg。

表 9.8. 内置转换

转换名称 ^[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`
`windows_1258_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_windows_1258`	`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`
`utf8_to_euc_jis_2004`	`UTF8`	`EUC_JIS_2004`
`shift_jis_2004_to_utf8`	`SHIFT_JIS_2004`	`UTF8`
`utf8_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`
^[a] 转换名称遵循标准命名规则：将源编码正式名称中的所有非字母数字字符替换为下划线，再接上 `_to_`，然后接上按相同方式处理的目标编码名称。因此，这些名称可能与惯用的编码名称不同。

上一页	上一级	下一页
9.3. 数学函数和操作符	起始页	9.5. 二进制串函数和操作符

报告文档问题

阅读上游文档. 反馈更正前请先核对当前版本手册.