> PostgreSQL9.6 中文手册 > 字符串函数和操作符

9.4. 字符串函数和操作符

本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型charactercharacter varyingtext的值。除非另外说明,所有下面列出的函数都可以处理这些类型,不过要小心的是,在使用character类型的时候, 它有自动填充空白的潜在影响。有些函数还可以处理位串类型。

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

注意: 由于存在从那些数据类型到text的隐式强制措施,在PostgreSQL 8.3之前,这些函数也可以接受多种非字符串数据类型。这些强制措施在目前的版本中已经被删除,因为它们常常导致令人惊讶的行为。不过,字符串串接操作符(||)仍然接受非字符串输入,只要至少一个输入是一种字符串类型,如表 9-8所示。对于其他情况,如果你需要复制之前的行为,可以为text插入一个显式强制措施。

表 9-8. SQL字符串函数和操作符

函数 返回类型 描述 例子 结果
string || string text 串接 'Post' || 'greSQL' PostgreSQL
string || non-string or non-string || string text 使用一个非字符串输入的串接 'Value: ' || 42 Value: 42
bit_length(string) 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正则表达式的子串。模式匹配详情见第 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的开头/结尾/两端删除最长的只包含characters(默认是一个空格)的串 trim(both 'x' from 'xTomxx') Tom
trim([leading | trailing | both] [from] string [, characters] ) text trim()的非标准版本 trim(both from 'xTomxx', 'x') Tom
upper(string) text 将字符串转换成大写形式 upper('tom') TOM

还有额外的串操作函数可以用,它们在表 9-9中列出。它们有些在内部用于实现表 9-8列出的SQL标准字符串函数。

表 9-9. 其他字符串函数

函数 返回类型 描述 例子 结果
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-10。 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 将二进制数据编码成一个文本表达。支持的格式有:base64hexescapeescape将零字节和高位组字节转换为八进制序列(\nnn)和双写的反斜线。 encode(E'123\\000\\001', 'base64') MTIzAAE=
format(formatstr text [, formatarg "any" [, ...] ]) text 根据一个格式字符串格式化参数。该函数和C函数sprintf相似。见第 9.4.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 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') trim
md5(string) text 计算string的 MD5 哈希,返回十六进制的结果 md5('abc') 900150983cd24fb0 d6963f7d28e17f72
parse_ident(qualified_identifier text [, strictmode boolean DEFAULT true ] ) text[] qualified_identifier分成一个标识符数组, 移除单个标识符上的任何引号。默认情况下, 最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false, 那么这一类多余的字符会被忽略(这种行为对于解析函数之类的对象名称有用)。 注意这个函数不会截断超长标识符。如果想要进行截断,可以把结果转换成name[] parse_ident('"SomeSchema".someTable') {SomeSchema,sometable}
pg_client_encoding() name 当前的客户端编码名字 pg_client_encoding() SQL_ASCII
quote_ident(string text) text 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个标识符。只有需要时才会加上引号(即,如果字符串包含非标识符字符或可能是大小写折叠的)。嵌入的引号会被正确地双写。参见例 41-1。 quote_ident('Foo bar') "Foo bar"
quote_literal(string text) text 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空;如果参数可能为空,quote_nullable通常更合适。参见例 41-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。嵌入的引号会被正确地双写。请参见例 41-1。 quote_nullable(NULL) NULL
quote_nullable(value anyelement) text 强迫给定值为文本并且接着将它用引号包围作为一个文本;或者,如果参数为空,返回NULL。嵌入的单引号和反斜线被正确的双写。 quote_nullable(42.5) '42.5'
regexp_matches(string text, pattern text [, flags text]) setof text[] 返回对string匹配一个POSIX正则表达式得到的所有子串。详见第 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', E'\\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', E'\\s+') hello

world

(2 rows)
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('trimxxxx', 'x') trim
split_part(string text, delimiter text, field int) text delimiter划分string并返回给定域(从1开始计算) 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(只支持从LATIN1LATIN2LATIN9WIN1250编码的转换) 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集合中的相应字符。如果fromto长,from中的额外字符会被删除。 translate('12345', '143', 'ax') a2x5

concatconcat_wsformat函数是可变的,因此可以把要串接或格式化的值作为一个标记了VARIADIC关键字的数组进行传递(见第 36.4.5 节)。数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为 NULL,concatconcat_ws返回 NULL,但format把 NULL 当作一个零元素数组。

还可以参阅第 9.20 节中的string_agg

表 9-10. 内建转换

转换名 [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
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.4.1. format

函数format根据一个格式字符串产生格式化的输出,其形式类似于 C 函数sprintf

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

formatstr是一个格式字符串,它指定了结果应该如何被格式化。格式字符串中的文本被直接复制到结果中,除了使用格式说明符的地方。格式说明符在字符串中扮演着占位符的角色,它定义后续的函数参数如何被格式化及插入到结果中。每一个formatarg参数会被根据其数据类型的常规输出规则转换为文本,并接着根据格式说明符被格式化和插入到结果字符串中。

格式说明符由一个%字符开始并且有这样的形式

%[position][flags][width]type

其中的各组件域是:

position(可选)

一个形式为n$的字符串,其中n是要打印的参数的索引。索引 1 表示formatstr之后的第一个参数。如果position被忽略,默认会使用序列中的下一个参数。

flags(可选)

控制格式说明符的输出如何被格式化的附加选项。当前唯一支持的标志是一个负号(-),它将导致格式说明符的输出会被左对齐(left-justified)。除非width域也被指定,否者这个域不会产生任何效果。

width(可选)

指定用于显示格式说明符输出的最小字符数。输出将被在左部或右部(取决于-标志)用空格填充以保证充满该宽度。太小的宽度设置不会导致输出被截断,但是会被简单地忽略。宽度可以使用下列形式之一指定:一个正整数;一个星号(*)表示使用下一个函数参数作为宽度;或者一个形式为*n$的字符串表示使用第n个函数参数作为宽度。

如果宽度来自于一个函数参数,则参数在被格式说明符的值使用之前就被消耗掉了。如果宽度参数是负值,结果会在长度为abs(width)的域中被左对齐(如果-标志被指定)。

type(必需)

格式转换的类型,用于产生格式说明符的输出。支持下面的类型:

  • s将参数值格式化为一个简单字符串。一个控制被视为一个空字符串。

  • I将参数值视作 SQL 标识符,并在必要时用双写引号包围它。如果参数为空,将会是一个错误(等同于quote_ident)。

  • L将参数值引用为 SQL 文字。一个空值将被显示为不带引号的字符串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', E'C:\\Program Files');
结果:INSERT INTO locations VALUES(E'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 函数sprintfPostgreSQLformat函数允许将带有或者不带有position域的格式说明符被混在同一个格式字符串中。一个不带有position域的格式说明符总是使用最后一个被消耗的参数的下一个参数。另外,format函数不要求所有函数参数都被用在格式字符串中。例如:

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
结果:Testing three, two, three

对于安全地构造动态 SQL 语句,%I%L格式说明符特别有用。参见例 41-1。