sql字符串函数和操作符

pgup12

　　本节描述了用于检查和操作字符串数值的函数和操作符。 在这个环境中的字串包括所有类型 character， character varying，和 text 的值。除非另外说明，所有下面列出的函数都可以处理这些类型， 不过要小心的是，在使用 character 类型的时候， 它的自动填充的潜在影响。通常这里描述的函数也能用于非字串 类型，我们只要先把那些数据转化为字串表现形式就可以了。 有些函数还可以处理位串类型。
　　SQL 定义了一些字串函数， 它们有指定的语法，它们里面是用 某种特定的关键字，而不是逗号来分隔参数。 详情请见Table 9-6， 这些函数也用正常的函数调用说法实现了。 （参阅 Table 9-7。）

　　Table 9-6. SQL 字串函数和操作符
函数返回类型描述例子结果string || stringtext 字串连接 'Post' || 'greSQL'PostgreSQLbit_length(string)integer字串里二进制位的个数bit_length('jose')32char_length(string) 或 character_length(string)integer字串中的字符个数 char_length('jose')4convert(string using conversion_name)text使用指定的转换名字改变编码。转换可以通过 CREATE CONVERSION 定义。当然系统里有一些预定义的转换名字。参阅 Table 9-8 获取可用的转换名。 convert('PostgreSQL'
using
iso_8859_1_to_utf_8)Unicode (UTF-8) 编码的'PostgreSQL'lower(string)text把字串转化为小写lower('TOM')tomoctet_length(string)integer字串中的字节数octet_length('jose')4position(substring in string)integer声明的子字串的位置position('om' in 'Thomas')3overlay(string placing string from integer [for integer])text替换子字串 overlay('Txxxxas' placing 'hom' from 2 for 4)Thomasposition(substring in string)integer指定的子字串的位置position('om' in 'Thomas')3substring(string [from integer] [for integer])text抽取子字串 substring('Thomas' from 2 for 3)homsubstring(string from pattern)text抽取匹配 POSIX 正则表达式的子字串 substring('Thomas' from '...$')massubstring(string from pattern for escape)text抽取匹配SQL正则表达式的子字串 substring('Thomas' from '%#"o_a#"_' for '#')omatrim([leading | trailing | both] [characters] from string) text从字串 string 的 开头/结尾/两边/ 删除只包含 characters （缺省是一个空白）的最长的字串。 trim(both 'x' from 'xTomxx')Tomupper(string)text把字串转化为大写。upper('tom')TOM　　还有额外的字串操作函数可以用，它们在Table 9-7列出。 它们有些在内部用于实现Table 9-6
列出的SQL标准字串函数。

　　Table 9-7. 其他字串函数
函数返回类型描述例子结果ascii(text)integer参数第一
个字符的 ASCII 码ascii('x')120btrim
(string text, characters text)text从 string
开头和结尾删除 只包含在 characters 里的字符的最长字串。 btrim
('xyxtrimyyx','xy')trimchr(integer)text给出 ASCII 码的字符chr(65)Aconvert(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
_unicodedecode(string text, type text) bytea把早先用encode编码的，存放在 string 里面的二进制数据解码。 参数类型和encode一样。 decode('MTIzAAE=',
'base64')123\000\001encode(data bytea, type text) text把二进制数据编码为只包含 ASCII 形式的数据。 支持的类型有base64，hex，escape。 encode('123\\000
\\001', 'base64')MTIzAAE=initcap(text)text把每个单词（空白分隔）的第一个子母转为大写initcap('hi
thomas')Hi Thomaslength(string)integer字串中字符的数目 length('jose')4lpad(string text, length integer [, fill text]) text通过填充字符 fill （缺省时为空白）， 把 string 填充为长度 length。 如果 string 已经比 length 长则将其截断（在右边）。 lpad('hi', 5, 'xy')xyxhiltrim(string text, characters text)text从字串 string 的 开头 删除只包含 characters 的最长的字串。 ltrim
('zzzytrim','xyz')trimmd5(string text)text计算给出字串的 MD5 散列，以十六进制返回结果。 md5('abc')90015098
3cd24
fb0d69
63f7d2
8e17f72pg_client_encoding()name当前客户端编码名称。 pg_client_
encoding()SQL_ASCIIquote_ident(string text)text返回给出字串的一个适用于在SQL语句字串里当作标识符引起使用的形式。 只有在必要的时候才会添加引号（也就是说，如果字串包含非标识符字符或者会 转换大小写的字符）。 嵌入的引号被恰当地写了双份。 quote_ident('Foo')"Foo"quote_literal(string text)text返回给出字串的一个适用于在SQL语句字串里当作文本使用的形式。 嵌入的引号和反斜杠被恰当地写了双份。 quote_literal
('O\'Reilly')'O''Reilly'repeat(text, integer)text重复 text 一定次数。repeat('Pg', 4)PgPgPgPgreplace(string text, from text, to text)text把字串string里出现地所有子字串 from 替换成子字串 to。 replace
('abcdefabcdef',
'cd', 'XX')abXXef
abXXefrpad(string text, length integer [, fill text]) text通过填充字符 fill （缺省时为空白）， 把 string 填充为长度 length。 如果 string 已经比 length 长则将其截断。 rpad('hi', 5, 'xy')hixyxrtrim(string
text, character text)text从字串 string 的 结尾
删除只包含 character 的最长的字串。 rtrim
('trimxxxx','x')trimsplit_part
(string text, delimiter text, field integer)text根据 delimiter 分隔 string 返回生成的第 field 个子字串（一为基）。 split_part
('abc~@~def
~@~ghi','~@~',2)defstrpos(string, substring)text声明的子字串的位置。（和 position(substring in string一样），不过要注意参数顺序 是相反的） strpos
('high','ig')2substr(string, from [, count])text抽取子字串。（和 substring(string from from for count)一样） substr('alphabet',
3, 2)phto_ascii
(text [, encoding])text把文本从其它编码转换为 ASCII。 [a] to_ascii('Karel')Karelto_hex(number integer 或者 bigint)text把 number 转换成其对应地十六进制表现形式。 to_hex(922337203
6854775807)7fffffff
fffffffftranslate(string text, from text, to text) text把在 string 中包含的任何匹配 from 中的字符的字符转化为对应的 在 to 中的字符。 translate('12345',
'14', 'ax')a23x5Notes:
a. to_ascii 函数只支持从 LATIN1， LATIN2，和 WIN1250 转换。

　　Table 9-8. 内置的转换
转换名 [a] 源编码目的编码ascii_to_micSQL_ASCIIMULE_INTERNALascii_to_utf_8SQL_ASCIIUNICODEbig5_to_euc_twBIG5EUC_TWbig5_to_micBIG5MULE_INTERNALbig5_to_utf_8BIG5UNICODEeuc_cn_to_micEUC_CNMULE_INTERNALeuc_cn_to_utf_8EUC_CNUNICODEeuc_jp_to_micEUC_JPMULE_INTERNALeuc_jp_to_sjisEUC_JPSJISeuc_jp_to_utf_8EUC_JPUNICODEeuc_kr_to_micEUC_KRMULE_INTERNALeuc_kr_to_utf_8EUC_KRUNICODEeuc_tw_to_big5EUC_TWBIG5euc_tw_to_micEUC_TWMULE_INTERNALeuc_tw_to_utf_8EUC_TWUNICODEgb18030_to_utf_8GB18030UNICODEgbk_to_utf_8GBKUNICODEiso_8859_10_to_utf_8LATIN6UNICODEiso_8859_13_to_utf_8LATIN7UNICODEiso_8859_14_to_utf_8LATIN8UNICODEiso_8859_15_to_utf_8LATIN9UNICODEiso_8859_16_to_utf_8LATIN10UNICODEiso_8859_1_to_micLATIN1MULE_INTERNALiso_8859_1_to_utf_8LATIN1UNICODEiso_8859_2_to_micLATIN2MULE_INTERNALiso_8859_2_to_utf_8LATIN2UNICODEiso_8859_2_to_windows_1250LATIN2WIN1250iso_8859_3_to_micLATIN3MULE_INTERNALiso_8859_3_to_utf_8LATIN3UNICODEiso_8859_4_to_micLATIN4MULE_INTERNALiso_8859_4_to_utf_8LATIN4UNICODEiso_8859_5_to_koi8_rISO_8859_5KOI8iso_8859_5_to_micISO_8859_5MULE_INTERNALiso_8859_5_to_utf_8ISO_8859_5UNICODEiso_8859_5_to_windows_1251ISO_8859_5WINiso_8859_5_to_windows_866ISO_8859_5ALTiso_8859_6_to_utf_8ISO_8859_6UNICODEiso_8859_7_to_utf_8ISO_8859_7UNICODEiso_8859_8_to_utf_8ISO_8859_8UNICODEiso_8859_9_to_utf_8LATIN5UNICODEjohab_to_utf_8JOHABUNICODEkoi8_r_to_iso_8859_5KOI8ISO_8859_5koi8_r_to_micKOI8MULE_INTERNALkoi8_r_to_utf_8KOI8UNICODEkoi8_r_to_windows_1251KOI8WINkoi8_r_to_windows_866KOI8ALTmic_to_asciiMULE_INTERNALSQL_ASCIImic_to_big5MULE_INTERNALBIG5mic_to_euc_cnMULE_INTERNALEUC_CNmic_to_euc_jpMULE_INTERNALEUC_JPmic_to_euc_krMULE_INTERNALEUC_KRmic_to_euc_twMULE_INTERNALEUC_TWmic_to_iso_8859_1MULE_INTERNALLATIN1mic_to_iso_8859_2MULE_INTERNALLATIN2mic_to_iso_8859_3MULE_INTERNALLATIN3mic_to_iso_8859_4MULE_INTERNALLATIN4mic_to_iso_8859_5MULE_INTERNALISO_8859_5mic_to_koi8_rMULE_INTERNALKOI8mic_to_sjisMULE_INTERNALSJISmic_to_windows_1250MULE_INTERNALWIN1250mic_to_windows_1251MULE_INTERNALWINmic_to_windows_866MULE_INTERNALALTsjis_to_euc_jpSJISEUC_JPsjis_to_micSJISMULE_INTERNALsjis_to_utf_8SJISUNICODEtcvn_to_utf_8TCVNUNICODEuhc_to_utf_8UHCUNICODEutf_8_to_asciiUNICODESQL_ASCIIutf_8_to_big5UNICODEBIG5utf_8_to_euc_cnUNICODEEUC_CNutf_8_to_euc_jpUNICODEEUC_JPutf_8_to_euc_krUNICODEEUC_KRutf_8_to_euc_twUNICODEEUC_TWutf_8_to_gb18030UNICODEGB18030utf_8_to_gbkUNICODEGBKutf_8_to_iso_8859_1UNICODELATIN1utf_8_to_iso_8859_10UNICODELATIN6utf_8_to_iso_8859_13UNICODELATIN7utf_8_to_iso_8859_14UNICODELATIN8utf_8_to_iso_8859_15UNICODELATIN9utf_8_to_iso_8859_16UNICODELATIN10utf_8_to_iso_8859_2UNICODELATIN2utf_8_to_iso_8859_3UNICODELATIN3utf_8_to_iso_8859_4UNICODELATIN4utf_8_to_iso_8859_5UNICODEISO_8859_5utf_8_to_iso_8859_6UNICODEISO_8859_6utf_8_to_iso_8859_7UNICODEISO_8859_7utf_8_to_iso_8859_8UNICODEISO_8859_8utf_8_to_iso_8859_9UNICODELATIN5utf_8_to_johabUNICODEJOHAButf_8_to_koi8_rUNICODEKOI8utf_8_to_sjisUNICODESJISutf_8_to_tcvnUNICODETCVNutf_8_to_uhcUNICODEUHCutf_8_to_windows_1250UNICODEWIN1250utf_8_to_windows_1251UNICODEWINutf_8_to_windows_1256UNICODEWIN1256utf_8_to_windows_866UNICODEALTutf_8_to_windows_874UNICODEWIN874windows_1250_to_iso_8859_2WIN1250LATIN2windows_1250_to_micWIN1250MULE_INTERNALwindows_1250_to_utf_8WIN1250UNICODEwindows_1251_to_iso_8859_5WINISO_8859_5windows_1251_to_koi8_rWINKOI8windows_1251_to_micWINMULE_INTERNALwindows_1251_to_utf_8WINUNICODEwindows_1251_to_windows_866WINALTwindows_1256_to_utf_8WIN1256UNICODEwindows_866_to_iso_8859_5ALTISO_8859_5windows_866_to_koi8_rALTKOI8windows_866_to_micALTMULE_INTERNALwindows_866_to_utf_8ALTUNICODEwindows_866_to_windows_1251ALTWINwindows_874_to_utf_8WIN874UNICODENotes:
a. 转换名遵循一个标准的命名模式：将源编码中的所有非字母数字字符 用下划线替换，后面跟着 _to_，然后后面再跟着 经过同样处理的目标编码的名字。因此这些名字可能和客户的编码名字 不同。