9.4. 字符串函数和操作符

本节描述了用于检查和操作字符串数值的函数和操作符。 在这个环境中的字符串包括所有charactercharacter varyingtext类型的值。 除非另外说明,所有下面列出的函数都可以处理这些类型, 不过要小心的是,在使用character类型的时候,需要注意自动填充的潜在影响。 通常这里描述的函数也能用于非字符串类型,我们只要先把那些数据转化为字符串表现形式就可 以了。有些函数还可以处理位串类型。

SQL定义了一些字符串函数,它们有指定的语法(用特定的关键字而不是逗号来分隔参数)。 详情参阅Table 9-5PostgreSQL也提供了这些函数的版本, 它们使用普通的函数调用语法。 (参阅Table 9-6)。

Note: PostgreSQL8.3之前, 这些函数将默默接受一些非字符串数据类型的值, 由于存在从这些数据类型到text的隐式强制转换,转换后的它们经常发生意外的行为,因此删除了隐式强制转换。 然而,字符串连接操作(||)仍接受非字符串输入,只要至少有一个输入一个字符串类型, 如显示在Table 9-5。对于其它情况下,如果你需要重复以前的行为, 插入一个明确的强制转换到text

Table 9-5. SQL字符串函数和操作符

函数返回类型描述示例结果
string|| stringtext 字符串连接 'Post' || 'greSQL'PostgreSQL
string|| non-stringnon-string|| string text 一个带有非字符串输入的字符串串联 'Value: ' || 42Value: 42
bit_length(string)int字符串的位bit_length('jose')32
char_length(string)orcharacter_length(string)int 字符串中的字符个数 char_length('jose')4
lower(string)text把字符串转化为小写lower('TOM')tom
octet_length(string)int字符串中的字节数octet_length('jose')4
overlay(stringplacingstringfromint[forint])text 替换子字符串 overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas
position(substringinstring)int指定子字符串的位置position('om' in 'Thomas')3
substring(string[fromint][forint])text 截取子字符串 substring('Thomas' from 2 for 3)hom
substring(stringfrompattern)text 截取匹配POSIX正则表达式的子字符串。参阅Section 9.7获取更多关于模式匹配的信息。 substring('Thomas' from '...$')mas
substring(stringfrompatternforescape)text 截取匹配SQL正则表达式的子字符串。参阅Section 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
upper(string)text把字符串转化为大写upper('tom')TOM

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

Table 9-6. 其它字符串函数

函数返回类型描述示例结果
ascii(string)int 获取参数中第一个字符的ASCII编码值。对于UTF8返回字符的宽字节编码值。 对于其它的多字节编码,参数必须是一个ASCII字符。 ascii('x')120
btrim(stringtext [characterstext])textstring开头和结尾删除只包含characters中字符(缺省是空白)的最长字符串。 btrim('xyxtrimyyx', 'xy')trim
chr(int)text 给定编码的字符。对于UTF8这个参数作为宽字节代码处理。对于其它的多字节编码, 这个参数必须指定一个ASCII字符,因为text数据类型无法存储NULL数据字节,不能将NULL(0)作为字符参数。 chr(65)A
convert(stringbyteasrc_encodingnamedest_encodingname) bytea 将字符串转化为dest_encoding格式的字符串。 src_encoding表示原始的编码格式。在这种编码格式中string必须是有效的。 用CREATE CONVERSION定义转换。这也有些预定义的转换。 参阅Table 9-7显示可用的转换。 convert('text_in_utf8', 'UTF8', 'LATIN1')text_in_utf8represented in Latin-1 encoding (ISO 8859-1)
convert_from(stringbyteasrc_encodingname) text 将字符串转化为数据库编码格式的,src_encoding表示原始的编码格式,这种编码格式中,string必须是有效的。 convert_from('text_in_utf8', 'UTF8')text_in_utf8represented in the current database encoding
convert_to(stringtextdest_encodingname) bytea 将字符串转化为dest_encoding编码格式。 convert_to('some text', 'UTF8')some textrepresented in the UTF8 encoding
decode(stringtexttypetext) bytea 把前边用encode编码的string里面的二进制数据解码。参数类型和encode相同。 decode('MTIzAAE=', 'base64')123\000\001
encode(databyteatypetext) text 把二进制数据编码为只包含ASCII形式的数据。支持的类型有base64hexescapeEscape输出空字节用\000和双反斜杠. encode(E'123\\000\\001', 'base64')MTIzAAE=
initcap(string)text 把每个单词的第一个子母转为大写,其它的保留小写。单词是一系列字母数字组成的字符,用非字母数字分隔。 initcap('hi THOMAS')Hi Thomas
length(string)int string中字符数 length('jose')4
length(stringbyteaencodingname)int 指定encoding编码格式的string的字符数。 在这个编码格式中,string必须是有效的。 length('jose', 'UTF8')4
lpad(stringtextlengthint [filltext]) text 通过填充字符fill(缺省时为空白),把string填充为length长度。 如果string已经比length长则将其尾部截断。 lpad('hi', 5, 'xy')xyxhi
ltrim(stringtext [characterstext]) text 从字符串string的开头删除只包含characters中字符(缺省为空白)的最长的字符串。 ltrim('zzzytrim', 'xyz')trim
md5(string)text 计算string的MD5散列,以十六进制返回结果。 md5('abc')900150983cd24fb0 d6963f7d28e17f72
pg_client_encoding()name 当前客户端编码名称 pg_client_encoding()SQL_ASCII
quote_ident(stringtext)text返回适用于SQL语句的标识符形式(使用适当的引号进行界定)。只有在必要的时候才会添加引号(字符串包含非标识符字符或者会转换大小写的字符)。 里面的单引号和反斜杠会处理为双份。也可以参阅Example 39-1quote_ident('Foo bar')"Foo bar"
quote_literal(stringtext)text返回适用于在SQL语句里当作文本使用的形式。嵌入的引号和反斜杠处理为双份。注意quote_literal当空值输入时返回空值;如果参数可能有空值, 用这个quote_nullable往往更合适。参阅Example 39-1quote_literal('O\'Reilly')'O''Reilly'
quote_literal(valueanyelement)text 将给定的值强制转换为text,加上引号作为文本。里面的单引号和反斜杠会处理为双份。 quote_literal(42.5)'42.5'
quote_nullable(stringtext)text 返回指定字符串适当的引用,作为一条SQL语句的字符串;或者,如果参数为空的话,返回NULL。 里面的单引号和反斜杠会处理为双份。参阅Example 39-1quote_nullable(NULL)NULL
quote_nullable(valueanyelement)text 将给定的参数值转化为text,加上引号作为文本;或者,如果参数为空的话,返回NULL。里面的单引号和反斜杠会处理为双份。 quote_nullable(42.5)'42.5'
regexp_matches(stringtextpatterntext[,flagstext])setof text[] 返回string中所有匹配POSIX正则表达式的子字符串。参阅Section 9.7.3获得更多模式匹配的信息。 regexp_matches('foobarbequebaz', '(bar)(beque)'){bar,beque}
regexp_replace(stringtextpatterntextreplacementtext[,flagstext])text 替换匹配POSIX正则表达式的子字符串。参阅Section 9.7.3以获取更多模式匹配的信息。 regexp_replace('Thomas', '.[mN]a.', 'M')ThM
regexp_split_to_array(stringtextpatterntext[,flagstext])text[] 用POSIX正则表达式作为分隔符,分隔string。参阅Section 9.7.3以获取更多模式匹配的信息。 regexp_split_to_array('hello world', E'\\s+'){hello,world}
regexp_split_to_table(stringtextpatterntext[,flagstext])setof text 用POSIX正则表达式作为分隔符,分隔string。参阅Section 9.7.3以获取更多模式匹配的信息。 regexp_split_to_table('hello world', E'\\s+')hello

world

(2 rows)
repeat(stringtextnumberint)textstring重复numberrepeat('Pg', 4)PgPgPgPg
replace(stringtextfromtexttotext)textReplace all occurrences instringof substring fromwith substringto 将字符串string里出现地所有子字符串from替换成子字符串toreplace('abcdefabcdef', 'cd', 'XX')abXXefabXXef
rpad(stringtextlengthint [filltext]) text使用填充字符fill(缺省时为空白),把string填充到length长度。 如果string已经比length长则将其从尾部截断。 rpad('hi', 5, 'xy')hixyx
rtrim(stringtext [characterstext]) text从字符串string的结尾删除只包含characters中字符(缺省是一个空白符)的最长的字符串。 rtrim('trimxxxx', 'x')trim
split_part(stringtextdelimitertextfieldint)textdelimiter分隔string返回给定的字段(1为基)。 split_part('abc~@~def~@~ghi', '~@~', 2)def
strpos(stringsubstring)int 指定的子字符串的位置。(同position(substringinstring),不过参数顺序相反。) strpos('high', 'ig')2
substr(stringfrom[count])text 截取子字符串。(同substring(stringfromfromforcount))) substr('alphabet', 3, 2)ph
to_ascii(stringtext [encodingtext])textstring从其它编码转换为ASCII(仅支持LATIN1LATIN2LATIN9WIN1250编码)。 to_ascii('Karel')Karel
to_hex(numberint orbigint)textnumber转换成十六进制表现形式 to_hex(2147483647)7fffffff
translate(stringtextfromtexttotext) textstring中任何匹配from字符集中的字符转化为对应的在to字符集的字符。 translate('12345', '14', 'ax')a23x5

See also the aggregate functionstring_aggin Section 9.18.

Table 9-7. 内置的转换

转换名 [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
ut8_to_euc_jis_2004UTF8EUC_JIS_2004
shift_jis_2004_to_utf8SHIFT_JIS_2004UTF8
ut8_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
Notes:
a. 转换名遵循一个标准的命名模式:将源编码的正式名称中所有非字母数字字符用下划线替换,后面跟着_to_, 然后再跟着经过同样处理的目标编码的名字。因此这些名字可能和客户的编码名字不同。