9.4. 字符串函数和操作符

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

SQL 定义了一些字串函数, 它们有指定的语法,它们里面是用 某种特定的关键字,而不是逗号来分隔参数。 详情请见Table 9-5, 这些函数也用正常的函数调用说法实现了。 (参阅 Table 9-6。)

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

函数返回类型描述例子结果
string || string text 字串连接 'Post' || 'greSQL'PostgreSQL
bit_length(string)integer字串里二进制位的个数bit_length('jose')32
char_length(string)character_length(string)integer 字串中的字符个数 char_length('jose')4
convert(string using conversion_name)text 使用指定的转换名字改变编码。转换可以通过 CREATE CONVERSION 定义。当然系统里有一些预定义的转换名字。参阅 Table 9-7 获取可用的转换名。 convert('PostgreSQL' using iso_8859_1_to_utf_8)Unicode (UTF-8) 编码的'PostgreSQL'
lower(string)text把字串转化为小写lower('TOM')tom
octet_length(string)integer字串中的字节数octet_length('jose')4
position(substring in string)integer声明的子字串的位置position('om' in 'Thomas')3
overlay(string placing string from integer [for integer])text 替换子字串 overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas
position(substring in string)integer指定的子字串的位置position('om' in 'Thomas')3
substring(string [from integer] [for integer])text 抽取子字串 substring('Thomas' from 2 for 3)hom
substring(string from pattern)text 抽取匹配 POSIX 正则表达式的子字串 substring('Thomas' from '...$')mas
substring(string from pattern for escape)text 抽取匹配SQL正则表达式的子字串 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(text)integer参数第一个字符的 ASCIIascii('x')120
btrim(string text [, characters text])textstring 开头和结尾删除只包含在 characters 里(缺省是空白)的字符的最长字串。 btrim('xyxtrimyyx','xy')trim
chr(integer)text给出 ASCII 码的字符chr(65)A
convert(string text, [src_encoding name,] dest_encoding name) text 把字串转换为 dest_encoding . 原来的编码是用 src_encoding 声明的. 如果省略了 src_encoding, 则假设为数据库编码. convert('text_in_unicode', 'UNICODE', 'LATIN1')以 ISO 8859-1 编码表示的text_in_unicode
decode(string text, type text) bytea 把早先用encode编码的,存放在 string 里面的二进制数据解码。 参数类型和encode一样。 decode('MTIzAAE=', 'base64')123\000\001
encode(data bytea, type text) text 把二进制数据编码为只包含 ASCII 形式的数据。 支持的类型有base64hexescapeencode('123\\000\\001', 'base64')MTIzAAE=
initcap(text)text 把每个单词的第一个子母转为大写,其它的保留小写。 单词是一系列字母数字组成的字符,用非字母数字分隔。 initcap('hi thomas')Hi Thomas
length(string text)integer string 中字符的数目 length('jose')4
lpad(string text, length integer [, fill text]) text 通过填充字符 fill (缺省时为空白), 把 string 填充为长度 length。 如果 string 已经比 length 长则将其截断(在右边)。 lpad('hi', 5, 'xy')xyxhi
ltrim(string text [, characters text]) text 从字串 string 的 开头删除只包含 characters (缺省是一个空白)的最长的字串。 ltrim('zzzytrim','xyz')trim
md5(string text)text 计算给出 string 的 MD5 散列,以十六进制返回结果。 md5('abc')900150983cd24fb0d6963f7d28e17f72
pg_client_encoding()name 当前客户端编码名称。 pg_client_encoding()SQL_ASCII
quote_ident(string text)text 返回给出字串的一个适用于在SQL语句字串里当作标识符引起使用的形式。 只有在必要的时候才会添加引号(也就是说,如果字串包含非标识符字符或者会转换大小写的字符)。 嵌入的引号被恰当地写了双份。 quote_ident('Foo bar')"Foo bar"
quote_literal(string text)text 返回给出字串的一个适用于在SQL语句字串里当作文本使用的形式。 嵌入的引号和反斜杠被恰当地写了双份。 quote_literal('O\'Reilly')'O''Reilly'
repeat(string text, number integer)text重复 string number 次。repeat('Pg', 4)PgPgPgPg
replace(string text, from text, to text)text把字串string里出现地所有子字串 from 替换成子字串 toreplace('abcdefabcdef', 'cd', 'XX')abXXefabXXef
rpad(string text, length integer [, fill text]) text 通过填充字符 fill (缺省时为空白), 把 string 填充为长度 length。 如果 string 已经比 length 长则将其截断。 rpad('hi', 5, 'xy')hixyx
rtrim(string text [, character text]) text 从字串 string 的 结尾删除只包含 character (缺省是个空白)的最长的字串。 rtrim('trimxxxx','x')trim
split_part(string text, delimiter text, field integer)text根据 delimiter 分隔 string 返回生成的第 field 个子字串(一为基)。 split_part('abc~@~def~@~ghi','~@~',2)def
strpos(string, substring)text 声明的子字串的位置。(和 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(text [, encoding])texttext 从其它编码转换为 ASCII[a] to_ascii('Karel')Karel
to_hex(number integer 或者 bigint)textnumber 转换成其对应地十六进制表现形式。 to_hex(9223372036854775807)7fffffffffffffff
translate(string text, from text, to text) text 把在 string 中包含的任何匹配 from 中的字符的字符转化为对应的 在 to 中的字符。 translate('12345', '14', 'ax')a23x5
Notes:
a. to_ascii 函数只支持从 LATIN1LATIN2LATIN9WIN1250 编码进行转换。

Table 9-7. 内置的转换

转换名 [a] 源编码目的编码
ascii_to_micSQL_ASCIIMULE_INTERNAL
ascii_to_utf_8SQL_ASCIIUNICODE
big5_to_euc_twBIG5EUC_TW
big5_to_micBIG5MULE_INTERNAL
big5_to_utf_8BIG5UNICODE
euc_cn_to_micEUC_CNMULE_INTERNAL
euc_cn_to_utf_8EUC_CNUNICODE
euc_jp_to_micEUC_JPMULE_INTERNAL
euc_jp_to_sjisEUC_JPSJIS
euc_jp_to_utf_8EUC_JPUNICODE
euc_kr_to_micEUC_KRMULE_INTERNAL
euc_kr_to_utf_8EUC_KRUNICODE
euc_tw_to_big5EUC_TWBIG5
euc_tw_to_micEUC_TWMULE_INTERNAL
euc_tw_to_utf_8EUC_TWUNICODE
gb18030_to_utf_8GB18030UNICODE
gbk_to_utf_8GBKUNICODE
iso_8859_10_to_utf_8LATIN6UNICODE
iso_8859_13_to_utf_8LATIN7UNICODE
iso_8859_14_to_utf_8LATIN8UNICODE
iso_8859_15_to_utf_8LATIN9UNICODE
iso_8859_16_to_utf_8LATIN10UNICODE
iso_8859_1_to_micLATIN1MULE_INTERNAL
iso_8859_1_to_utf_8LATIN1UNICODE
iso_8859_2_to_micLATIN2MULE_INTERNAL
iso_8859_2_to_utf_8LATIN2UNICODE
iso_8859_2_to_windows_1250LATIN2WIN1250
iso_8859_3_to_micLATIN3MULE_INTERNAL
iso_8859_3_to_utf_8LATIN3UNICODE
iso_8859_4_to_micLATIN4MULE_INTERNAL
iso_8859_4_to_utf_8LATIN4UNICODE
iso_8859_5_to_koi8_rISO_8859_5KOI8
iso_8859_5_to_micISO_8859_5MULE_INTERNAL
iso_8859_5_to_utf_8ISO_8859_5UNICODE
iso_8859_5_to_windows_1251ISO_8859_5WIN
iso_8859_5_to_windows_866ISO_8859_5ALT
iso_8859_6_to_utf_8ISO_8859_6UNICODE
iso_8859_7_to_utf_8ISO_8859_7UNICODE
iso_8859_8_to_utf_8ISO_8859_8UNICODE
iso_8859_9_to_utf_8LATIN5UNICODE
johab_to_utf_8JOHABUNICODE
koi8_r_to_iso_8859_5KOI8ISO_8859_5
koi8_r_to_micKOI8MULE_INTERNAL
koi8_r_to_utf_8KOI8UNICODE
koi8_r_to_windows_1251KOI8WIN
koi8_r_to_windows_866KOI8ALT
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_INTERNALKOI8
mic_to_sjisMULE_INTERNALSJIS
mic_to_windows_1250MULE_INTERNALWIN1250
mic_to_windows_1251MULE_INTERNALWIN
mic_to_windows_866MULE_INTERNALALT
sjis_to_euc_jpSJISEUC_JP
sjis_to_micSJISMULE_INTERNAL
sjis_to_utf_8SJISUNICODE
tcvn_to_utf_8TCVNUNICODE
uhc_to_utf_8UHCUNICODE
utf_8_to_asciiUNICODESQL_ASCII
utf_8_to_big5UNICODEBIG5
utf_8_to_euc_cnUNICODEEUC_CN
utf_8_to_euc_jpUNICODEEUC_JP
utf_8_to_euc_krUNICODEEUC_KR
utf_8_to_euc_twUNICODEEUC_TW
utf_8_to_gb18030UNICODEGB18030
utf_8_to_gbkUNICODEGBK
utf_8_to_iso_8859_1UNICODELATIN1
utf_8_to_iso_8859_10UNICODELATIN6
utf_8_to_iso_8859_13UNICODELATIN7
utf_8_to_iso_8859_14UNICODELATIN8
utf_8_to_iso_8859_15UNICODELATIN9
utf_8_to_iso_8859_16UNICODELATIN10
utf_8_to_iso_8859_2UNICODELATIN2
utf_8_to_iso_8859_3UNICODELATIN3
utf_8_to_iso_8859_4UNICODELATIN4
utf_8_to_iso_8859_5UNICODEISO_8859_5
utf_8_to_iso_8859_6UNICODEISO_8859_6
utf_8_to_iso_8859_7UNICODEISO_8859_7
utf_8_to_iso_8859_8UNICODEISO_8859_8
utf_8_to_iso_8859_9UNICODELATIN5
utf_8_to_johabUNICODEJOHAB
utf_8_to_koi8_rUNICODEKOI8
utf_8_to_sjisUNICODESJIS
utf_8_to_tcvnUNICODETCVN
utf_8_to_uhcUNICODEUHC
utf_8_to_windows_1250UNICODEWIN1250
utf_8_to_windows_1251UNICODEWIN
utf_8_to_windows_1256UNICODEWIN1256
utf_8_to_windows_866UNICODEALT
utf_8_to_windows_874UNICODEWIN874
windows_1250_to_iso_8859_2WIN1250LATIN2
windows_1250_to_micWIN1250MULE_INTERNAL
windows_1250_to_utf_8WIN1250UNICODE
windows_1251_to_iso_8859_5WINISO_8859_5
windows_1251_to_koi8_rWINKOI8
windows_1251_to_micWINMULE_INTERNAL
windows_1251_to_utf_8WINUNICODE
windows_1251_to_windows_866WINALT
windows_1256_to_utf_8WIN1256UNICODE
windows_866_to_iso_8859_5ALTISO_8859_5
windows_866_to_koi8_rALTKOI8
windows_866_to_micALTMULE_INTERNAL
windows_866_to_utf_8ALTUNICODE
windows_866_to_windows_1251ALTWIN
windows_874_to_utf_8WIN874UNICODE
Notes:
a. 转换名遵循一个标准的命名模式:将源编码中的所有非字母数字字符 用下划线替换,后面跟着 _to_,然后后面再跟着 经过同样处理的目标编码的名字。因此这些名字可能和客户的编码名字 不同。