postgresql 字符串函数和操作符

字符串函数和操作符

本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型character、character varying和text的值。除非另外说明，所有下面列出的函数都可以处理这些类型，不过要小心的是，在使用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的开头、结尾、两端（默认是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])	text	从string的开头或结尾删除最长的只包含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.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	将二进制数据编码成一个文本表达。支持的格式有：base64、hex、escape。escape将零字节和高位组字节转换为八进制序列（\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('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.1。	quote_ident('Foo bar')	"Foo bar"
quote_literal(string text)	text	将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空；如果参数可能为空，quote_nullable通常更合适。参见例 42.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。嵌入的引号会被正确地双写。请参见例 42.1。	quote_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指定的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('testxxzx', 'xyz')	test
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（只支持从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

 

concat、concat_ws和format函数是可变的，因此可以把要串接或格式化的值作为一个标记了VARIADIC关键字的数组进行传递（见第 37.4.5 节）。数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为 NULL，concat和concat_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_，最后是按照相似方式处理过的目标编码名称。因此，名称可能会不同于习惯的编码名称。

 

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 函数sprintf，PostgreSQL的format函数允许将带有或者不带有position域的格式说明符被混在同一个格式字符串中。一个不带有position域的格式说明符总是使用最后一个被消耗的参数的下一个参数。另外，format函数不要求所有函数参数都被用在格式字符串中。例如：

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

 

对于安全地构造动态 SQL 语句，%I和%L格式说明符特别有用。