9.4. 字符串函数和操作符

9.4.1. format

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

SQL定义了一些字符串函数,它们使用关键字,而不是逗号来分隔参数。详情请见表 9.8PostgreSQL也提供了这些函数使用正常函数调用语法的版本(见表 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: ' || 42Value: 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) textstring的开头、结尾、两端(默认是both) 删除最长的只包含来自characters字符(默认是一个空格)的串 trim(both 'xyz' from 'yxTomxx')Tom
trim([leading | trailing | both] [from] string [, characters] ) text trim()的非标准语法 trim(both from 'yxTomxx', 'xyz')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]) textstring的开头或结尾删除最长的只包含characters(默认是一个空格)的串 btrim('xyxtrimyyx', 'xyz')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.10convert('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_encodingconvert_to('some text', 'UTF8')用UTF8编码表达的some text
decode(string text, format text) byteastring中的文本表达解码二进制数据。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]) textstring通过前置字符fill(默认是一个空格)填充到长度length。如果string已经长于length,则它被(从右边)截断。 lpad('hi', 5, 'xy')xyxhi
ltrim(string text [, characters text]) textstring的开头删除最长的只包含characters(默认是一个空格)的串 ltrim('zzzytest', 'xyz')test
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语句字符串中被用作一个标识符。只有需要时才会加上引号(即,如果字符串包含非标识符字符或可能是大小写折叠的)。嵌入的引号会被正确地双写。参见例 42.1quote_ident('Foo bar')"Foo bar"
quote_literal(string text) text 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空;如果参数可能为空,quote_nullable通常更合适。参见例 42.1quote_literal(E'O\'Reilly')'O''Reilly'
quote_literal(value anyelement)text 强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写。 quote_literal(42.5)'42.5'
quote_nullable(string text) text 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个字符串文字;或者,如果参数为空,返回NULL。嵌入的引号会被正确地双写。请参见例 42.1quote_nullable(NULL)NULL
quote_nullable(value anyelement)text 强迫给定值为文本并且接着将它用引号包围作为一个文本;或者,如果参数为空,返回NULL。嵌入的单引号和反斜线被正确的双写。 quote_nullable(42.5)'42.5'
regexp_match(string text, pattern text [, flags text]) text[] 将从POSIX正则表达式首次匹配产生的捕获子字符串返回给string。 有关更多信息,请参见第 9.7.3 节regexp_match('foobarbequebaz', '(bar)(beque)'){bar,beque}
regexp_matches(string text, pattern text [, flags text]) setof text[] 将从POSIX正则表达式匹配产生的捕获子字符串返回给string。 详见第 9.7.3 节regexp_matches('foobarbequebaz', 'ba.', 'g'){bar}

{baz}

(2 rows)
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指定的numberrepeat('Pg', 4)PgPgPgPg
replace(string text, from text, to text) textstring中出现的所有子串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]) textstring通过增加字符fill(默认为一个空格)填充到长度length。如果string已经长于length则它会被截断。 rpad('hi', 5, 'xy')hixyx
rtrim(string text [, characters text]) textstring的结尾删除最长的只包含characters(默认是一个空格)的串 rtrim('testxxzx', 'xyz')test
split_part(string text, delimiter text, field int) textdelimiter划分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]) textstring从另一个编码转换到ASCII(只支持从LATIN1LATIN2LATIN9WIN1250编码的转换) to_ascii('Karel')Karel
to_hex(number int or bigint) textnumber转换到它等效的十六进制表示 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关键字的数组进行传递(见第 37.4.5 节)。数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为 NULL,concatconcat_ws返回 NULL,但format把 NULL 当作一个零元素数组。

还可以参阅第 9.20 节中的string_agg

表 9.10. 内建转换

转换名 [a] 源编码目标编码
ascii_to_micSQL_ASCIIMULE_INTERNAL
ascii_to_utf8SQL_ASCIIUTF8
big5_to_euc_twBIG5EUC_TW
big5_to_micBIG5MULE_INTERNAL
big5_to_utf8BIG5UTF8
euc_cn_to_micEUC_CNMULE_INTERNAL
euc_cn_to_utf8EUC_CNUTF8
euc_jp_to_micEUC_JPMULE_INTERNAL
euc_jp_to_sjisEUC_JPSJIS
euc_jp_to_utf8EUC_JPUTF8
euc_kr_to_micEUC_KRMULE_INTERNAL
euc_kr_to_utf8EUC_KRUTF8
euc_tw_to_big5EUC_TWBIG5
euc_tw_to_micEUC_TWMULE_INTERNAL
euc_tw_to_utf8EUC_TWUTF8
gb18030_to_utf8GB18030UTF8
gbk_to_utf8GBKUTF8
iso_8859_10_to_utf8LATIN6UTF8
iso_8859_13_to_utf8LATIN7UTF8
iso_8859_14_to_utf8LATIN8UTF8
iso_8859_15_to_utf8LATIN9UTF8
iso_8859_16_to_utf8LATIN10UTF8
iso_8859_1_to_micLATIN1MULE_INTERNAL
iso_8859_1_to_utf8LATIN1UTF8
iso_8859_2_to_micLATIN2MULE_INTERNAL
iso_8859_2_to_utf8LATIN2UTF8
iso_8859_2_to_windows_1250LATIN2WIN1250
iso_8859_3_to_micLATIN3MULE_INTERNAL
iso_8859_3_to_utf8LATIN3UTF8
iso_8859_4_to_micLATIN4MULE_INTERNAL
iso_8859_4_to_utf8LATIN4UTF8
iso_8859_5_to_koi8_rISO_8859_5KOI8R
iso_8859_5_to_micISO_8859_5MULE_INTERNAL
iso_8859_5_to_utf8ISO_8859_5UTF8
iso_8859_5_to_windows_1251ISO_8859_5WIN1251
iso_8859_5_to_windows_866ISO_8859_5WIN866
iso_8859_6_to_utf8ISO_8859_6UTF8
iso_8859_7_to_utf8ISO_8859_7UTF8
iso_8859_8_to_utf8ISO_8859_8UTF8
iso_8859_9_to_utf8LATIN5UTF8
johab_to_utf8JOHABUTF8
koi8_r_to_iso_8859_5KOI8RISO_8859_5
koi8_r_to_micKOI8RMULE_INTERNAL
koi8_r_to_utf8KOI8RUTF8
koi8_r_to_windows_1251KOI8RWIN1251
koi8_r_to_windows_866KOI8RWIN866
koi8_u_to_utf8KOI8UUTF8
mic_to_asciiMULE_INTERNALSQL_ASCII
mic_to_big5MULE_INTERNALBIG5
mic_to_euc_cnMULE_INTERNALEUC_CN
mic_to_euc_jpMULE_INTERNALEUC_JP
mic_to_euc_krMULE_INTERNALEUC_KR
mic_to_euc_twMULE_INTERNALEUC_TW
mic_to_iso_8859_1MULE_INTERNALLATIN1
mic_to_iso_8859_2MULE_INTERNALLATIN2
mic_to_iso_8859_3MULE_INTERNALLATIN3
mic_to_iso_8859_4MULE_INTERNALLATIN4
mic_to_iso_8859_5MULE_INTERNALISO_8859_5
mic_to_koi8_rMULE_INTERNALKOI8R
mic_to_sjisMULE_INTERNALSJIS
mic_to_windows_1250MULE_INTERNALWIN1250
mic_to_windows_1251MULE_INTERNALWIN1251
mic_to_windows_866MULE_INTERNALWIN866
sjis_to_euc_jpSJISEUC_JP
sjis_to_micSJISMULE_INTERNAL
sjis_to_utf8SJISUTF8
tcvn_to_utf8WIN1258UTF8
uhc_to_utf8UHCUTF8
utf8_to_asciiUTF8SQL_ASCII
utf8_to_big5UTF8BIG5
utf8_to_euc_cnUTF8EUC_CN
utf8_to_euc_jpUTF8EUC_JP
utf8_to_euc_krUTF8EUC_KR
utf8_to_euc_twUTF8EUC_TW
utf8_to_gb18030UTF8GB18030
utf8_to_gbkUTF8GBK
utf8_to_iso_8859_1UTF8LATIN1
utf8_to_iso_8859_10UTF8LATIN6
utf8_to_iso_8859_13UTF8LATIN7
utf8_to_iso_8859_14UTF8LATIN8
utf8_to_iso_8859_15UTF8LATIN9
utf8_to_iso_8859_16UTF8LATIN10
utf8_to_iso_8859_2UTF8LATIN2
utf8_to_iso_8859_3UTF8LATIN3
utf8_to_iso_8859_4UTF8LATIN4
utf8_to_iso_8859_5UTF8ISO_8859_5
utf8_to_iso_8859_6UTF8ISO_8859_6
utf8_to_iso_8859_7UTF8ISO_8859_7
utf8_to_iso_8859_8UTF8ISO_8859_8
utf8_to_iso_8859_9UTF8LATIN5
utf8_to_johabUTF8JOHAB
utf8_to_koi8_rUTF8KOI8R
utf8_to_koi8_uUTF8KOI8U
utf8_to_sjisUTF8SJIS
utf8_to_tcvnUTF8WIN1258
utf8_to_uhcUTF8UHC
utf8_to_windows_1250UTF8WIN1250
utf8_to_windows_1251UTF8WIN1251
utf8_to_windows_1252UTF8WIN1252
utf8_to_windows_1253UTF8WIN1253
utf8_to_windows_1254UTF8WIN1254
utf8_to_windows_1255UTF8WIN1255
utf8_to_windows_1256UTF8WIN1256
utf8_to_windows_1257UTF8WIN1257
utf8_to_windows_866UTF8WIN866
utf8_to_windows_874UTF8WIN874
windows_1250_to_iso_8859_2WIN1250LATIN2
windows_1250_to_micWIN1250MULE_INTERNAL
windows_1250_to_utf8WIN1250UTF8
windows_1251_to_iso_8859_5WIN1251ISO_8859_5
windows_1251_to_koi8_rWIN1251KOI8R
windows_1251_to_micWIN1251MULE_INTERNAL
windows_1251_to_utf8WIN1251UTF8
windows_1251_to_windows_866WIN1251WIN866
windows_1252_to_utf8WIN1252UTF8
windows_1256_to_utf8WIN1256UTF8
windows_866_to_iso_8859_5WIN866ISO_8859_5
windows_866_to_koi8_rWIN866KOI8R
windows_866_to_micWIN866MULE_INTERNAL
windows_866_to_utf8WIN866UTF8
windows_866_to_windows_1251WIN866WIN
windows_874_to_utf8WIN874UTF8
euc_jis_2004_to_utf8EUC_JIS_2004UTF8
utf8_to_euc_jis_2004UTF8EUC_JIS_2004
shift_jis_2004_to_utf8SHIFT_JIS_2004UTF8
utf8_to_shift_jis_2004UTF8SHIFT_JIS_2004
euc_jis_2004_to_shift_jis_2004EUC_JIS_2004SHIFT_JIS_2004
shift_jis_2004_to_euc_jis_2004SHIFT_JIS_2004EUC_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格式说明符特别有用。参见例 42.1