Postgresql 中文操作指南

9.4. String Functions and Operators #

本节介绍用于检查和操作字符串值的功能和操作符。此上下文中的字符串包括类型_character_、character varying_和_text_的值。在未注明的情况下，这些功能和操作符声明接受和返回类型 _text。它们将可交换地接受_character varying_参数。character_类型的将值在应用功能或操作符之前会转换为_text，从而删除_character_中所有尾随空格。

SQL 定义了一些字符串函数，这些函数使用关键字而不是逗号来分隔参数。详细信息请参阅 Table 9.9。PostgreSQL 还提供了使用常规函数调用语法（请参阅 Table 9.10）的这些函数版本。

Note

连接符（||）将接受非字符串输入，只要其中至少一个输入为字符串类型，如 Table 9.9所示。对于其他情况，插入到_text_的显式强制转换可用于接受非字符串输入。

Table 9.9. SQL String Functions and Operators

Function/Operator

Description

Example(s)

text _

_ text → text 将两个字符串连接起来。_'Post'

'greSQL'_ → PostgreSQL

text _

_ anynonarray → text anynonarray _

_ text → text 将非字符串输入转换为文本，然后将两个字符串连接起来。（非字符串输入不能为数组类型，因为这样会与数组 _

_ 运算符产生歧义。如果您想要连接数组的文本等价项，请将其显式强制转换为 text 。）_'Value: '

42_ → Value: 42

btrim ( string text [, characters text ] ) → text 从 string 的开头和末尾移除最长的字符串（默认情况下为一个空格），该字符串仅包含 characters 中的字符。 btrim('xyxtrimyyx', 'xyz') → trim

text IS [ NOT ] [ form ] NORMALIZED → boolean 检查字符串是否为指定 Unicode 规范化格式。可选的 form 关键字指定格式： NFC （默认）、 NFD 、 NFKC 或 NFKD 。此表达式只能在服务器编码为 UTF8 时使用。请注意，使用此表达式检查规范化通常比规范化可能已经规范化的字符串更快。 U&'\0061\0308bc' IS NFD NORMALIZED → t

bit_length ( text ) → integer 返回字符串中的位数（8 倍 octet_length ）。 bit_length('jose') → 32

char_length ( text ) → integer character_length ( text ) → integer 返回字符串中的字符数。 char_length('josé') → 4

lower ( text ) → text 根据数据库的区域设置规则，将字符串转换为全小写。 lower('TOM') → tom

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('zzzytest', 'xyz') → test

normalize ( text [, form ] ) → text 将字符串转换为指定的 Unicode 规范化形式。可选的 form 关键字指定形式： NFC （默认） NFD NFKC ， NFKD 。此函数仅在服务器编码为 UTF8 时才能使用。 normalize(U&'\0061\0308bc', NFC) → U&'\00E4bc'

octet_length ( text ) → integer 返回字符串中的字节数。 octet_length('josé') → 5 （如果服务器编码为 UTF8）

octet_length ( character ) → integer 返回字符串中的字节数。由于此版本的函数直接接受类型 character ，因此它不会去掉尾部的空格。 octet_length('abc '::character(4)) → 4

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text 替换 string 中从第 start ' 个字符开始且包含 count 个字符的子字符串，替换为 newsubstring 。如果 count 被忽略，它将默认为 newsubstring 的长度。 overlay('Txxxxas' placing 'hom' from 2 for 4) → Thomas

position ( substring text IN string text ) → integer 返回指定 substring 在 string 中的第一个起始索引，如果它不存在，则返回零。 position('om' in 'Thomas') → 3

rpad ( string text , length integer [, 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

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text 提取 string 的子字符串，如果指定，则从第 start ' 个字符开始，如果指定，则在 count 个字符后停止。提供 start 和 count 中的至少一个。 substring('Thomas' from 2 for 3) → hom substring('Thomas' from 3) → omas substring('Thomas' for 2) → Th

substring ( string text FROM pattern text ) → text 提取与 POSIX 正则表达式匹配的第一个子字符串；请参阅 Section 9.7.3 。 substring('Thomas' from '…​$') → mas

substring ( string text SIMILAR pattern text ESCAPE escape text ) → text substring ( string text FROM pattern text FOR escape text ) → text 提取与 SQL 正则表达式匹配的第一个子字符串；请参阅 Section 9.7.2 。第一种形式已自 SQL:2003 起指定；第二种形式仅在 SQL:1999 中指定，应视为已过时。 substring('Thomas' similar '%"o_a" ' 转义 '#')_ → oma

trim ( [ LEADING

TRAILING

BOTH ] [ characters text ] FROM string text ) → text 从 string 的开头、结尾或开头和结尾（默认情况下为 BOTH ）中移除仅包含 characters （默认情况下，包含空格）中的字符的最长字符串。 trim(both 'xyz' from 'yxTomxx') → Tom

trim ( [ LEADING

TRAILING

BOTH ] [ FROM ] string text [, characters text ] ) → text 这是 trim() 的非标准语法。 trim(both from 'yxTomxx', 'xyz') → Tom

upper ( text ) → text 根据数据库的区域设置规则，将字符串转换为全大写。 upper('tom') → TOM

还有其他字符串操作函数和运算符可用，它们列在 Table 9.10。（其中一些函数在内部用于实现列在 Table 9.9 中的 SQL 标准字符串函数。）还有模式匹配运算符，在 Section 9.7中有描述，还有全文搜索运算符，在 Chapter 12中有描述。

Table 9.10. Other String Functions and Operators

Function/Operator

Description

Example(s)

text ^@ text → boolean 如果第一个字符串以第二个字符串开头，则返回 true（相当于 starts_with() 函数）。 'alphabet' ^@ 'alph' → t

ascii ( text ) → integer 返回参数第一个字符的数字代码。在 UTF8 编码中，返回字符的 Unicode 代码点。在其他多字节编码中，参数必须是 ASCII 字符。 ascii('x') → 120

chr ( integer ) → text 返回具有给定代码的字符。在 UTF8 编码中，参数被视为 Unicode 代码点。在其他多字节编码中，参数必须指定 ASCII 字符。 chr(0) 不被允许，因为文本数据类型不能存储该字符。 chr(65) → A

concat ( val1 "any" [, val2 "any" [, …​] ] ) → text 将所有参数的文本表示连接起来。忽略 NULL 参数。 concat('abcde', 2, NULL, 22) → abcde222

concat_ws ( sep text , val1 "any" [, val2 "any" [, …​] ] ) → text 将除第一个参数外的所有参数连接起来，并带有分隔符。第一个参数用作分隔符字符串，不应为 NULL。忽略其他 NULL 参数。 concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22

format ( formatstr text [, formatarg "any" [, …​] ] ) → text 根据格式化字符串格式化参数；请参阅 Section 9.4.1 。此函数类似于 C 函数 sprintf 。 format('Hello %s, %1$s', 'World') → Hello World, World

initcap ( text ) → text 将每个单词的首字母转换为大写，其余转换为小写。单词是字母数字字符序列，由非字母数字字符分隔。 initcap('hi THOMAS') → Hi Thomas

left ( string text , n integer ) → text 返回字符串第一个 n 字符，如果 n 为负数，则返回除了最后一个字符以外的所有字符。

n

characters.

left('abcde', 2) → ab

length ( text ) → integer 返回字符串中字符的数量。 length('jose') → 4

md5 ( text ) → text 以十六进制数形式计算参数的 MD5 hash 。 md5('abc') → 900150983cd24fb0​d6963f7d28e17f72

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[] 将 qualified_identifier 拆分为一个标识符数组，删除单个标识符的任何引用。默认情况下，最后一个标识符之后的额外字符被视为错误；但如果第二个参数是 false ，则忽略此类额外字符。（这种行为对于解析函数等对象的名称很有用。）请注意，此函数不会截断超长标识符。如果要截断，可以将结果转换为 name[] 。 parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable}

pg_client_encoding ( ) → name 返回当前客户端编码名称。 pg_client_encoding() → UTF8

quote_ident ( text ) → text 返回给定的字符串，该字符串经过适当引用，可在 SQL 语句字符串中用作标识符。仅在必要时添加引号（即，如果字符串包含非标识符字符或会折叠大小写）。正确加倍嵌入的引号。另请参阅 Example 43.1 。 quote_ident('Foo bar') → "Foo bar"

quote_literal ( text ) → text 返回给定的字符串，该字符串经过适当引用，可在 SQL 语句字符串中用作字符串文字。正确加倍嵌入的单引号和反斜杠。请注意， quote_literal 在空输入时返回 null；如果参数可能是 null， quote_nullable 通常更合适。另请参阅 Example 43.1 。 quote_literal(E’O’Reilly') → 'O''Reilly'

quote_literal ( anyelement ) → text 将给定值转换为文本，然后将其作为文字引用。正确加倍嵌入的单引号和反斜杠。 quote_literal(42.5) → '42.5'

quote_nullable ( text ) → text 返回给定的字符串，该字符串经过适当引用，可在 SQL 语句字符串中用作字符串文字；或者，如果参数为 null，则返回 NULL 。正确加倍嵌入的单引号和反斜杠。另请参阅 Example 43.1 。 quote_nullable(NULL) → NULL

quote_nullable ( anyelement ) → text 将给定值转换为文本，然后将其作为文字引用；或者，如果参数为 null，则返回 NULL 。正确加倍嵌入的单引号和反斜杠。 quote_nullable(42.5) → '42.5'

regexp_count ( string text , pattern text [, start integer [, flags text ] ] ) → integer 返回 POSIX 正则表达式 pattern 在 string 中匹配的次数；请参阅 Section 9.7.3 。 regexp_count('123456789012', '\d\d\d', 2) → 3

regexp_instr ( string text , pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer 返回 string 中 POSIX 正则表达式 pattern 的 N '个匹配项的位置，如果不存在此类匹配项，则返回零；请参阅 Section 9.7.3 。 regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i') → 3 regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i', 2) → 5

regexp_like ( string text , pattern text [, flags text ] ) → boolean 检查 POSIX 正则表达式 pattern 的匹配是否发生在 string 中；请参阅 Section 9.7.3 。 regexp_like('Hello World', 'world$', 'i') → t

regexp_match ( string text , pattern text [, flags text ] ) → text[] 返回 POSIX 正则表达式 pattern 的第一个匹配项中的子字符串；请参阅 Section 9.7.3 。 regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}

regexp_matches ( string text , pattern text [, flags text ] ) → setof text[] 返回 POSIX 正则表达式 pattern 的第一个匹配项中的子字符串，如果使用 g 标志，则返回所有此类匹配中的子字符串；请参阅 Section 9.7.3 。 regexp_matches('foobarbequebaz', 'ba.', 'g') → {bar} {baz}

regexp_replace ( string text , pattern text , replacement text [, start integer ] [, flags text ] ) → text 替换子字符串，它是与 POSIX 正则表达式 pattern 的第一次匹配，或者如果使用 g 标志，则替换所有此类匹配；参见 Section 9.7.3 。 regexp_replace('Thomas', '.[mN]a.', 'M') → ThM

regexp_replace ( string text , pattern text , replacement text , start integer , N integer [, flags text ] ) → text 替换子字符串，它是与 POSIX 正则表达式 pattern 的 N 匹配，或者如果 N 为零，则替换所有此类匹配；参见 Section 9.7.3 。 regexp_replace('Thomas', '.', 'X', 3, 2) → ThoXas

regexp_split_to_array ( string text , pattern text [, flags text ] ) → text[] 使用 POSIX 正则表达式作为分隔符拆分 string ，生成结果数组；参见 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 使用 POSIX 正则表达式作为分隔符拆分 string ，生成结果集；参见 Section 9.7.3 。 regexp_split_to_table('hello world', '\s+') → hello world

regexp_substr ( string text , pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ] ) → text 返回 string 中匹配与 POSIX 正则表达式 pattern 第 N 个出现的子字符串，或者如果没有此类匹配，则返回 NULL ；参见 Section 9.7.3 。 regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i') → CDEF regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i', 2) → EF

repeat ( string text , number integer ) → text 重复 string 以 number 指定次数。 repeat('Pg', 4) → PgPgPgPg

replace ( string text , from text , to text ) → text 替换 string 中子字符串 from 所有出现为子字符串 to 。 replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef

reverse ( text ) → text 反转字符串中字符的顺序。 reverse('abcde') → edcba

right ( string text , n integer ) → text 返回字符串中最后 n 个字符，或者当 n 为负数时，返回第一个以外的所有字符

n

characters.

right('abcde', 2) → de

split_part ( string text , delimiter text , n integer ) → text 拆分 string 在 delimiter 出现的位置，并返回第 n 个字段（从一个开始计数），或者当 n 为负数时，返回

n

倒数第 split_part('abc@def@ghi', '@', 2) 个字段。 split_part('abc,def,ghi,jkl', ',', -2) → ghi

starts_with ( string text , prefix text ) → boolean 如果 string 以 prefix 开头，则返回 true。 starts_with('alphabet', 'alph') → t

string_to_array ( string text , delimiter text [, null_string text ] ) → text[] 在 delimiter 出现的位置拆分 string ，并将结果字段组成一个 text 数组。如果 delimiter 是 NULL ，则 string 中的每个字符都将成为数组中的一个单独的元素。如果 delimiter 是一个空字符串，则 string 被视为一个单独的字段。如果提供 null_string 并且它不是 NULL ，则与该字符串匹配的字段将被 NULL 替换。另请参阅 array_to_string 。 string_to_array('xx~yy~zz', '~~', 'yy') → {xx,NULL,zz}

string_to_table ( string text , delimiter text [, null_string text ] ) → setof text 将 string 拆分为 delimiter 出现的次数，并返回结果字段作为 text 行的集合。如果 delimiter 是 NULL ，则 string 中的每个字符都将成为结果中的单独一行。如果 delimiter 是空字符串，则 string 将被视为单个字段。如果提供 null_string 并且不为 NULL ，则与该字符串匹配的字段将被 NULL 替换。 string_to_table('xxyyzz', '^', 'yy') → xx NULL zz

strpos ( string text , substring text ) → integer 返回在 string 中指定的 substring 的第一个开始索引，如果不存在，则返回零。（与 string 中的 position(_substring 相同），但请注意参数顺序相反。） strpos('high', 'ig') → 2

substr ( string text , start integer [, count integer ] ) → text 摘取从 start 字符开始的 string 子字符串，如果指定，则扩展 count 字符。（与 start 中的 substring(_string 相同）。 substr('alphabet', 3) → phabet substr('alphabet', 3, 2) → ph

to_ascii ( string text ) → text to_ascii ( string text , encoding name ) → text to_ascii ( string text , encoding integer ) → text 从另一个编码中将 string 转换为 ASCII，该编码可以通过名称或数字识别。如果省略 encoding ，则假定为数据库编码（在实践中这是唯一有用的情况）。转换主要包括删除重音。仅支持从 LATIN1 、 LATIN2 、 LATIN9 和 WIN1250 编码进行转换。（参见 unaccent 模块以获取另一种更灵活的解决方案。） to_ascii('Karél') → Karel

to_hex ( integer ) → text to_hex ( bigint ) → text 将数字转换为其等效的十六进制表示。 to_hex(2147483647) → 7fffffff

translate ( string text , from text , to text ) → text 将 string 中与 from 集中的字符匹配的每个字符替换为 to 集中的相应字符。如果 from 比 to 长，则会删除 from 中多余字符的出现。 translate('12345', '143', 'ax') → a2x5

unistr （ text ）→At{s2} 对参数中的转义 Unicode 字符进行计算。Unicode 字符可指定为 XXXX_ (4 个十六进制数字) 或 _\+_XXXXXX (6 hexadecimal digits), \u_XXXX (4 hexadecimal digits), or \U_XXXXXXXX （8 个十六进制数字）。若要指定反斜杠，则写两个反斜杠。所有其他字符都将按字面意义获取。如果服务器编码不是 UTF-8，则这些转义序列之一所确定的 Unicode 代码点将转换为实际服务器编码；如果无法转换，则会报告错误。该函数提供了一种（非标准）替代方法，用于带有 Unicode 转义字符的字符串常量（参见 Section 4.1.2.3 ）。 _unistr('d\0061t\+000061') → data unistr('d\u0061t\U00000061') → data

concat、concat_ws 和 format 函数是可变参数的，因此可以将要串联或要格式化为数组的值传递，该数组用 VARIADIC 关键字进行标记（请参见 Section 38.5.6）。数组的元素将会被视为函数的单独普通参数。如果可变参数数组为 NULL，则 concat 和 concat_ws 将返回 NULL，但 format 将 NULL 视为零元素数组。

另请参见 Section 9.21 中的聚合函数 string_agg，以及在 Table 9.13 中用于在字符串和 bytea 类型之间进行转换的函数。

9.4.1. format #

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

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

formatstr 是指定结果应如何格式化的格式字符串。直接将格式字符串中的文本复制到结果中，其中 format specifiers 除外。格式说明符充当字符串中的占位符，定义后续函数参数应如何格式化并插入到结果中。每个 formatarg 参数都根据其数据类型的常规输出规则转换为文本，然后根据格式说明符对其进行格式化并在结果字符串中进行插入。

格式说明符由 % 字符引入，且形式如下：

%[position][flags][width]type

其中，组件域为：

  • position (optional)

    • 形式为 _n $_ 的字符串，其中 n 是要打印的参数的索引。索引 1 表示 formatstr 后的第一个参数。如果省略了 position ，则默认使用序列中的下一个参数。

  • flags (optional)

    • 其他控制格式说明符输出的格式化方式的选项。当前支持的唯一标志是连字符 ( - )，它会使格式说明符的输出进行左对齐。除非 width 字段也已指定，否则这不会产生任何影响。

  • width (optional)

    • 指定用于显示格式说明符输出的字符的 minimum 数量。根据需要，在左侧或右侧填充输出（取决于 - 标志）空格以填充宽度。过小的宽度不会导致截断输出，而是直接被忽略。可以使用以下任何方法指定宽度：正整数；星号 ( * ) 用作下一个函数参数作为宽度；或 *_n $_ 形式的字符串用作第 n 个函数参数作为宽度。

    • 如果宽度来自于函数参数，则该参数在用于格式说明符的值的参数之前会被使用。如果宽度参数为负数，则结果为左对齐（如同指定了 - 标志）在长度为 abs 的字段中（ width ）。

  • type (required)

    • 格式转换类型用于生成格式说明符的输出。支持以下类型：

除了上面描述的格式说明符以外，可以使用特殊序列 %% 输出字面 % 字符。

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

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。