9.4. 字符串函数和运算符

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

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

Note

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

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

FunctionReturn TypeDescriptionExampleResult
string || stringtext字符串串联'Post' || 'greSQL'PostgreSQL
string || non-stringnon-string || stringtext带一个非字符串 Importing 的字符串连接'Value: ' || 42Value: 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])textReplace substringoverlay('Txxxxas' placing 'hom' from 2 for 4)Thomas
position(substring in string)int指定子字符串的位置position('om' in 'Thomas')3
substring(string [from int] [for int])textExtract substringsubstring('Thomas' from 2 for 3)hom
substring(string from pattern)text提取与 POSIX 正则表达式匹配的子字符串。有关模式匹配的更多信息,请参见Section 9.7substring('Thomas' from '...$')mas
substring(string from pattern for escape)text提取与 SQL 正则表达式匹配的子字符串。有关模式匹配的更多信息,请参见Section 9.7substring('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] )texttrim()的非标准语法trim(both from 'yxTomxx', 'xyz')Tom
upper(string)text将字符串转换为大写upper('tom')TOM

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

表 9.9. 其他字符串函数

FunctionReturn TypeDescriptionExampleResult
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.10convert('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将二进制数据编码为文本表示形式。支持的格式为:base64hexescapeescape将零字节和高位设置字节转换为八进制序列(\ * nnn *),并将反斜杠加倍。encode('123\000\001', 'base64')MTIzAAE=
format(formatstr text [, formatarg "any" [, ...] ])text根据格式字符串设置参数格式。此函数类似于 C 函数sprintf。参见Section 9.4.1format('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 42.1quote_ident('Foo bar')"Foo bar"
quote_literal(string text)text返回适当引用的给定字符串,以用作 SQL 语句字符串中的字符串 Literals。嵌入式单引号和反斜杠已正确加倍。请注意,quote_literal在 Importing 为 null 时返回 null;如果参数可能为 null,则quote_nullable通常更合适。另请参见Example 42.1quote_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 42.1quote_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.3regexp_match('foobarbequebaz', '(bar)(beque)'){bar,beque}
regexp_matches(string text, pattern text [, flags text])setof text[]返回将 POSIX 正则表达式与* string *匹配而获得的捕获子字符串。有关更多信息,请参见Section 9.7.3regexp_matches('foobarbequebaz', 'ba.', 'g'){bar}

{baz}
(2 rows)
regexp_replace(string text, pattern text, replacement text [, flags text])text替换与 POSIX 正则表达式匹配的子字符串。有关更多信息,请参见Section 9.7.3regexp_replace('Thomas', '.[mN]a.', 'M')ThM
regexp_split_to_array(string text, pattern text [, flags text ])text[]Split * string *,使用 POSIX 正则表达式作为分隔符。有关更多信息,请参见Section 9.7.3regexp_split_to_array('hello world', '\s+'){hello,world}
regexp_split_to_table(string text, pattern text [, flags text])setof textSplit * string *,使用 POSIX 正则表达式作为分隔符。有关更多信息,请参见Section 9.7.3regexp_split_to_table('hello world', '\s+')hello
world
(2 rows)
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
to_ascii(string text [, encoding text])text将* string *从另一种编码转换为 ASCII(仅支持从LATIN1LATIN2LATIN9WIN1250编码转换)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

concatconcat_wsformat函数是可变参数,因此可以将要串联或格式化的值传递为标有VARIADIC关键字的数组(请参见Section 37.4.5)。将数组的元素视为函数的单独普通参数。如果可变参数数组参数为 NULL,则concatconcat_ws返回 NULL,但是format将 NULL 视为零元素数组。

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

表 9.10. 内置转换

转换名称[a]Source EncodingDestination Encoding
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
windows_1258_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_windows_1258UTF8WIN1258
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
utf8_to_euc_jis_2004UTF8EUC_JIS_2004
shift_jis_2004_to_utf8SHIFT_JIS_2004UTF8
utf8_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
[a]转换名称遵循标准的命名方案:源编码的正式名称,所有非字母数字字符都用下划线代替,然后是_to_,然后是经过类似处理的目标编码名称。因此,名称可能与习惯的编码名称有所不同。

9.4.1. format

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

format(formatstr text [, formatarg "any" [, ...] ])
  • formatstr 是用于指定结果格式的格式字符串。格式字符串中的文本会直接复制到结果中,除非使用 format specifiers 。格式说明符充当字符串中的占位符,定义应如何格式化后续函数参数并将其插入结果中。每个 formatarg *参数根据其数据类型的常规输出规则转换为文本,然后根据格式说明符进行格式化并插入到结果字符串中。

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

%[position][flags][width]type

组件字段为:

    • position *(可选)
    • 格式为n$的字符串,其中* n 是要打印的参数的索引。索引 1 表示 formatstr 之后的第一个参数。如果省略 position *,则默认为按 Sequences 使用下一个参数。
    • flags *(可选)
    • 其他选项,用于控制格式说明符输出的格式。当前唯一受支持的标志是减号(-),这将导致格式说明符的输出向左对齐。除非也指定了* width *字段,否则这无效。
    • width *(可选)
    • 指定用于显示格式说明符输出的最少个字符。在输出的左侧或右侧(取决于-标志)填充空格,以填充宽度。太小的宽度不会导致输出被截断,而是会被忽略。可以使用以下任意一种方式指定宽度:正整数;星号(*),用于将下一个函数参数用作宽度;或格式为*n$的字符串,以将* n *函数参数用作宽度。

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

    • type *(必填)
    • 用于产生格式说明符输出的格式转换类型。支持以下类型:
  • s将参数值格式化为简单字符串。空值将被视为空字符串。

  • I将参数值视为 SQL 标识符,必要时将其双引号引起来。该值为 null(等于quote_ident)是错误的。

  • L引用参数值作为 SQLLiterals。空值显示为字符串NULL,不带引号(等效于quote_nullable)。

除上述格式说明符外,特殊序列%%可用于输出 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 42.1