9.4. 字符串函数和操作符
本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型character
、character varying
和text
的值。除非另外说明,所有下面列出的函数都可以处理这些类型,不过要小心的是,在使用character
类型的时候, 它有自动填充空白的潜在影响。有些函数还可以处理位串类型。
SQL定义了一些字符串函数,它们使用关键字,而不是逗号来分隔参数。详情请见表 9.8,PostgreSQL也提供了这些函数使用正常函数调用语法的版本(见表 9.9)。
注意
由于存在从那些数据类型到text
的隐式强制措施,在PostgreSQL 8.3之前,这些函数也可以接受多种非字符串数据类型。这些强制措施在目前的版本中已经被删除,因为它们常常导致令人惊讶的行为。不过,字符串串接操作符(||
)仍然接受非字符串输入,只要至少一个输入是一种字符串类型,如表 9.8所示。对于其他情况,如果你需要复制之前的行为,可以为text
插入一个显式强制措施。
表 9.8. SQL字符串函数和操作符
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
| text | 串接 | 'Post' || 'greSQL' | PostgreSQL |
or | text | 使用一个非字符串输入的串接 | 'Value: ' || 42 | Value: 42 |
| int | 串中的位数 | bit_length('jose') | 32 |
or | int | 串中字符数 | char_length('jose') | 4 |
| text | 将字符串转换为小写形式 | lower('TOM') | tom |
| int | 串中的字节数 | octet_length('jose') | 4 |
| text | 替换子串 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
| int | 定位指定子串 | position('om' in 'Thomas') | 3 |
| text | 提取子串 | substring('Thomas' from 2 for 3) | hom |
| text | 提取匹配POSIX正则表达式的子串。模式匹配详情见第 9.7 节。 | substring('Thomas' from '...$') | mas |
| text | 提取匹配SQL正则表达式的子串。模式匹配详情见第 9.7 节。 | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
| text | 从string 的开头、结尾、两端(默认是both ) 删除最长的只包含来自characters 字符(默认是一个空格)的串 | trim(both 'xyz' from 'yxTomxx') | Tom |
| text | trim() 的非标准语法 | trim(both from 'yxTomxx', 'xyz') | Tom |
| text | 将字符串转换成大写形式 | upper('tom') | TOM |
还有额外的串操作函数可以用,它们在表 9.9中列出。它们有些在内部用于实现表 9.8列出的SQL标准字符串函数。
表 9.9. 其他字符串函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
| int | 参数第一个字符的ASCII代码。对于UTF8返回该字符的Unicode代码点。对于其他多字节编码,该参数必须是一个ASCII字符。 | ascii('x') | 120 |
| text | 从string 的开头或结尾删除最长的只包含characters (默认是一个空格)的串 | btrim('xyxtrimyyx', 'xyz') | trim |
| text | 给定代码的字符。对于UTF8该参数被视作一个Unicode代码点。对于其他多字节编码该参数必须指定一个ASCII字符。NULL (0) 字符不被允许,因为文本数据类型不能存储这种字节。 | chr(65) | A |
| text | 串接所有参数的文本表示。NULL 参数被忽略。 | concat('abcde', 2, NULL, 22) | abcde222 |
| text | 将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略。 | concat_ws(',', 'abcde', 2, NULL, 22) | abcde,2,22 |
| bytea | 将字符串转换为dest_encoding 。原始编码由src_encoding 指定。string 在这个编码中必须可用。转换可以使用CREATE CONVERSION 定义。也有一些预定义的转换。可用的转换请见表 9.10。 | convert('text_in_utf8', 'UTF8', 'LATIN1') | 用Latin-1编码(ISO 8859-1) 表示的text_in_utf8 |
| text | 将字符串转换为数据库编码。原始编码由src_encoding 指定。string 在这个编码中必须可用。 | convert_from('text_in_utf8', 'UTF8') | 用当前数据库编码表示的text_in_utf8 |
| bytea | 将字符串转换为dest_encoding 。 | convert_to('some text', 'UTF8') | 用UTF8编码表达的some text |
| bytea | 从string 中的文本表达解码二进制数据。format 的选项和encode 中的一样。 | decode('MTIzAAE=', 'base64') | \x3132330001 |
| text | 将二进制数据编码成一个文本表达。支持的格式有:base64 、hex 、escape 。escape 将零字节和高位组字节转换为八进制序列(\ nnn )和双写的反斜线。 | encode(E'123\\000\\001', 'base64') | MTIzAAE= |
| text | 根据一个格式字符串格式化参数。该函数和C函数sprintf 相似。见第 9.4.1 节。 | format('Hello %s, %1$s', 'World') | Hello World, World |
| text | 将每一个词的第一个字母转换为大写形式并把剩下的字母转换为小写形式。词是由非字母数字字符分隔的字母数字字符的序列。 | initcap('hi THOMAS') | Hi Thomas |
| text | 返回字符串中的前n 个字符。当n 为负时,将返回除了最后|n |个字符之外的所有字符。 | left('abcde', 2) | ab |
| int | string 中的字符数 | length('jose') | 4 |
| int | string 在给定编码 中的字符数。string 必须在这个编码中有效。 | length('jose', 'UTF8') | 4 |
| text | 将string 通过前置字符fill (默认是一个空格)填充到长度length 。如果string 已经长于length ,则它被(从右边)截断。 | lpad('hi', 5, 'xy') | xyxhi |
| text | 从string 的开头删除最长的只包含characters (默认是一个空格)的串 | ltrim('zzzytest', 'xyz') | test |
| text | 计算string 的 MD5 哈希,返回十六进制的结果 | md5('abc') | 900150983cd24fb0 d6963f7d28e17f72 |
| text[] | 把qualified_identifier 分成一个标识符数组, 移除单个标识符上的任何引号。默认情况下, 最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false , 那么这一类多余的字符会被忽略(这种行为对于解析函数之类的对象名称有用)。 注意这个函数不会截断超长标识符。如果想要进行截断,可以把结果转换成name[] 。 | parse_ident('"SomeSchema".someTable') | {SomeSchema,sometable} |
| name | 当前的客户端编码名字 | pg_client_encoding() | SQL_ASCII |
| text | 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个标识符。只有需要时才会加上引号(即,如果字符串包含非标识符字符或可能是大小写折叠的)。嵌入的引号会被正确地双写。参见例 42.1。 | quote_ident('Foo bar') | "Foo bar" |
| text | 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal 对空输入返回空;如果参数可能为空,quote_nullable 通常更合适。参见例 42.1。 | quote_literal(E'O\'Reilly') | 'O''Reilly' |
| text | 强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写。 | quote_literal(42.5) | '42.5' |
| text | 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个字符串文字;或者,如果参数为空,返回NULL 。嵌入的引号会被正确地双写。请参见例 42.1。 | quote_nullable(NULL) | NULL |
| text | 强迫给定值为文本并且接着将它用引号包围作为一个文本;或者,如果参数为空,返回NULL 。嵌入的单引号和反斜线被正确的双写。 | quote_nullable(42.5) | '42.5' |
| text[] | 将从POSIX正则表达式首次匹配产生的捕获子字符串返回给string 。 有关更多信息,请参见第 9.7.3 节。 | regexp_match('foobarbequebaz', '(bar)(beque)') | {bar,beque} |
| setof text[] | 将从POSIX正则表达式匹配产生的捕获子字符串返回给string 。 详见第 9.7.3 节。 | regexp_matches('foobarbequebaz', 'ba.', 'g') | {bar}
|
| text | 替换匹配一个POSIX正则表达式的子串。详见第 9.7.3 节。 | regexp_replace('Thomas', '.[mN]a.', 'M') | ThM |
| text[] | 使用一个POSIX正则表达式作为分隔符划分string 。详见第 9.7.3 节。 | regexp_split_to_array('hello world', E'\\s+') | {hello,world} |
| setof text | 使用一个POSIX正则表达式作为分隔符划分string 。详见第 9.7.3 节。 | regexp_split_to_table('hello world', E'\\s+') | hello
|
| text | 重复string 指定的number 次 | repeat('Pg', 4) | PgPgPgPg |
| text | 将string 中出现的所有子串from 替换为子串to | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
| text | 返回反转的字符串。 | reverse('abcde') | edcba |
| text | 返回字符串中的最后n 个字符。如果n 为负,返回除最前面的|n |个字符外的所有字符。 | right('abcde', 2) | de |
| text | 将string 通过增加字符fill (默认为一个空格)填充到长度length 。如果string 已经长于length 则它会被截断。 | rpad('hi', 5, 'xy') | hixyx |
| text | 从string 的结尾删除最长的只包含characters (默认是一个空格)的串 | rtrim('testxxzx', 'xyz') | test |
| text | 按delimiter 划分string 并返回给定域(从1开始计算) | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
| int | 指定子串的位置(和position( 相同,但是注意相反的参数顺序) | strpos('high', 'ig') | 2 |
| text | 提取子串(与substring( 相同) | substr('alphabet', 3, 2) | ph |
| text | 将string 从另一个编码转换到ASCII(只支持从LATIN1 、LATIN2 、LATIN9 和WIN1250 编码的转换) | to_ascii('Karel') | Karel |
| text | 将number 转换到它等效的十六进制表示 | to_hex(2147483647) | 7fffffff |
| 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] 转换名遵循一种标准命名模式:将全部非字母数字字符替换为下划线的源编码的官方名称,后面跟上 |
9.4.1. format
函数format
根据一个格式字符串产生格式化的输出,其形式类似于 C 函数sprintf
。
format
(formatstrtext
[, formatarg"any"
[, ...] ])
formatstr
是一个格式字符串,它指定了结果应该如何被格式化。格式字符串中的文本被直接复制到结果中,除了使用格式说明符的地方。格式说明符在字符串中扮演着占位符的角色,它定义后续的函数参数如何被格式化及插入到结果中。每一个formatarg
参数会被根据其数据类型的常规输出规则转换为文本,并接着根据格式说明符被格式化和插入到结果字符串中。
格式说明符由一个%
字符开始并且有这样的形式
%[position][flags][width]type
其中的各组件域是:
-
一个形式为
的字符串,其中n
$n
是要打印的参数的索引。索引 1 表示formatstr
之后的第一个参数。如果position
被忽略,默认会使用序列中的下一个参数。 -
控制格式说明符的输出如何被格式化的附加选项。当前唯一支持的标志是一个负号(
-
),它将导致格式说明符的输出会被左对齐(left-justified)。除非width
域也被指定,否者这个域不会产生任何效果。 -
指定用于显示格式说明符输出的最小字符数。输出将被在左部或右部(取决于
-
标志)用空格填充以保证充满该宽度。太小的宽度设置不会导致输出被截断,但是会被简单地忽略。宽度可以使用下列形式之一指定:一个正整数;一个星号(*
)表示使用下一个函数参数作为宽度;或者一个形式为*
的字符串表示使用第n
$n
个函数参数作为宽度。如果宽度来自于一个函数参数,则参数在被格式说明符的值使用之前就被消耗掉了。如果宽度参数是负值,结果会在长度为
abs
(width
)的域中被左对齐(如果-
标志被指定)。 -
格式转换的类型,用于产生格式说明符的输出。支持下面的类型:
-
s
将参数值格式化为一个简单字符串。一个控制被视为一个空字符串。 -
I
将参数值视作 SQL 标识符,并在必要时用双写引号包围它。如果参数为空,将会是一个错误(等同于quote_ident
)。 -
L
将参数值引用为 SQL 文字。一个空值将被显示为不带引号的字符串NULL
(等同于quote_nullable
)。
-
position
(可选)
flags
(可选)
width
(可选)
type
(必需)
除了以上所述的格式说明符之外,要输出一个文字形式的%
字符,可以使用特殊序列%%
。
下面有一些基本的格式转换的例子:
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
格式说明符特别有用。参见例 42.1。