9.4. 字符串函数和运算符

本节介绍了用于检查和操作字符串值的函数和运算符。在这种情况下，字符串包括character，character varying和text类型的值。除非另有说明，否则下面列出的所有功能均可在所有这些类型上使用，但请注意使用character类型时自动填充空格的潜在影响。对于位串类型，本机还存在一些功能。

SQL 定义了一些使用关键字而不是逗号分隔参数的字符串函数。详细信息在Table 9.8中。 PostgreSQL 还提供了使用常规函数调用语法的这些函数的版本(请参见Table 9.9)。

Note

在 PostgreSQL 8.3 之前，由于存在从这些数据类型到text的隐式强制，这些函数也将静默接受几种非字符串数据类型的值。这些强制已被删除，因为它们经常引起令人惊讶的行为。但是，字符串连接运算符(||)仍接受非字符串 Importing，只要至少一个 Importing 为字符串类型即可，如Table 9.8所示。对于其他情况，如果您需要复制以前的行为，则对text插入显式强制。

表 9.8. SQL 字符串函数和运算符

Function Return Type Description Example Result
string || string text 字符串串联 'Post' || 'greSQL' PostgreSQL
string || non-string或non-string || string text 带一个非字符串 Importing 的字符串连接 'Value: ' || 42 Value: 42
bit_length(string) int 字符串中的位数 bit_length('jose') 32
char_length(string)或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 Replace substring 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 Extract substring substring('Thomas' from 2 for 3) hom
substring(string from pattern) text 提取与 POSIX 正则表达式匹配的子字符串。有关模式匹配的更多信息，请参见Section 9.7。 substring('Thomas' from '...$') mas
substring(string from pattern for escape) text 提取与 SQL 正则表达式匹配的子字符串。有关模式匹配的更多信息，请参见Section 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

其他字符串操作功能可用，并在Table 9.9中列出。其中一些用于内部以实现Table 9.8中列出的 SQL 标准字符串函数。

表 9.9. 其他字符串函数

Function Return Type Description Example Result
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定义。也有一些 sched 义的转换。有关可用的转化，请参见Table 9.10。 convert('text_in_utf8', 'UTF8', 'LATIN1') text_in_utf8以 Latin-1 编码表示(ISO 8859-1)
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') some text以 UTF8 编码表示
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('123\000\001', 'base64') MTIzAAE=
format(formatstr text [, formatarg "any" [, ...] ]) text 根据格式字符串设置参数格式。此函数类似于 C 函数sprintf。参见Section 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 给定* encoding 中 string *中的字符数。 * string *在此编码中必须有效。 length('jose', 'UTF8') 4
lpad(string text, length int [, fill text]) text 通过在字符* fill 之前加一个字符 fill (默认为空格)，将 string 填充为长度 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 当前 Client 端编码名称 pg_client_encoding() SQL_ASCII
quote_ident(string text) text 返回适当引用的给定字符串，以用作 SQL 语句字符串中的标识符。仅在必要时才添加引号(即，如果字符串包含非标识符字符或将大小写折叠)。嵌入式引号正确加倍。另请参见Example 43.1。 quote_ident('Foo bar') "Foo bar"
quote_literal(string text) text 返回适当引用的给定字符串，以用作 SQL 语句字符串中的字符串 Literals。嵌入式单引号和反斜杠已正确加倍。请注意，quote_literal在 Importing 为 null 时返回 null；如果参数可能为 null，则quote_nullable通常更合适。另请参见Example 43.1。 quote_literal(E'O\'Reilly') 'O''Reilly'
quote_literal(value anyelement) text 将给定值强制转换为文本，然后将其引用为 Literals。嵌入式单引号和反斜杠已正确加倍。 quote_literal(42.5) '42.5'
quote_nullable(string text) text 返回给定的字符串，该字符串适当地用引号括起来用作 SQL 语句字符串中的字符串 Literals；或者，如果参数为 null，则返回NULL。嵌入式单引号和反斜杠已正确加倍。另请参见Example 43.1。 quote_nullable(NULL) NULL
quote_nullable(value anyelement) text 将给定值强制转换为文本，然后将其引用为 Literals；或者，如果参数为 null，则返回NULL。嵌入式单引号和反斜杠已正确加倍。 quote_nullable(42.5) '42.5'
regexp_match(string text, pattern text [, flags text]) text[] 返回从 POSIX 正则表达式的第一个匹配项到* string *的捕获子字符串。有关更多信息，请参见Section 9.7.3。 regexp_match('foobarbequebaz', '(bar)(beque)') {bar,beque}
regexp_matches(string text, pattern text [, flags text]) setof text[] 返回将 POSIX 正则表达式与* string *匹配而获得的捕获子字符串。有关更多信息，请参见Section 9.7.3。 regexp_matches('foobarbequebaz', 'ba.', 'g') {bar}

{baz}
(2 行)
regexp_replace(string text, pattern text, replacement text [, flags text]) text 替换与 POSIX 正则表达式匹配的子字符串。有关更多信息，请参见Section 9.7.3。 regexp_replace('Thomas', '.[mN]a.', 'M') ThM
regexp_split_to_array(string text, pattern text [, flags text ]) text[] Split * string *，使用 POSIX 正则表达式作为分隔符。有关更多信息，请参见Section 9.7.3。 regexp_split_to_array('hello world', '\s+') {hello,world}
regexp_split_to_table(string text, pattern text [, flags text]) setof text Split * string *，使用 POSIX 正则表达式作为分隔符。有关更多信息，请参见Section 9.7.3。 regexp_split_to_table('hello world', '\s+') hello
world
(2 行)
repeat(string text, number int) text 重复* string 指定的 number *次 repeat('Pg', 4) PgPgPgPg
replace(string text, from text, to text) text 将子字符串* from 的 string 中的所有出现替换为子字符串 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 通过附加字符* fill (默认为空格)来将 string 填充为长度 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)相同，但请注意参数 Sequences 相反) strpos('high', 'ig') 2
substr(string, from [, count]) text 提取子字符串(与substring(string from from for count)相同) substr('alphabet', 3, 2) ph
starts_with(string, prefix) bool 如果* string 以 prefix *开头，则返回 true。 starts_with('alphabet', 'alph') t
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关键字的数组(请参见Section 38.5.5)。将数组的元素视为函数的单独普通参数。如果可变参数数组参数为 NULL，则concat和concat_ws返回 NULL，但是format将 NULL 视为零元素数组。

另请参见Section 9.20中的聚合函数string_agg。

表 9.10. 内置转换

转换名称[a] Source Encoding Destination Encoding
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
windows_1258_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_windows_1258 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_，然后是经过类似处理的目标编码名称。因此，名称可能与习惯的编码名称有所不同。

9.4.1. format

函数format以类似于 C 函数sprintf的样式生成根据格式字符串格式化的输出。

format(formatstr text [, formatarg "any" [, ...] ])

格式说明符由%字符引入，格式为

%[position][flags][width]type

组件字段为：

如果宽度来自函数参数，则该参数将在用于格式说明符值的参数之前使用。如果 width 参数为负，则结果在长度为abs(* width *)的字段中保持对齐(就像已指定-标志)。

除上述格式说明符外，特殊序列%%可用于输出 Literals%字符。

以下是一些基本格式转换的示例：

SELECT format('Hello %s', 'World');
Result: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
Result: INSERT INTO locations VALUES('C:\Program Files')

以下是使用* width *字段和-标志的示例：

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

SELECT format('|%*s|', 10, 'foo');
Result: |       foo|

SELECT format('|%*s|', -10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Result: |foo       |

这些示例显示对* position *字段的使用：

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: |       foo|

与标准 C 函数sprintf不同，PostgreSQL 的format函数允许带有* position 字段和不带有 position 字段的格式说明符混合在同一格式字符串中。没有 position *字段的格式说明符始终使用最后一个参数之后的下一个参数。另外，format函数不需要在格式字符串中使用所有函数参数。例如：

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three

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

上一章 首页 下一章