PostgreSQL 教程

Appearance

9.4. 字符串函数和运算符# ​

9.4.1. format

本节介绍用于检查和操作字符串值的函数和运算符。此上下文中的字符串包括类型character、character varying和text的值。除非另有说明，否则这些函数和运算符声明为接受并返回类型text。它们将可互换地接受character varying参数。类型为character的值将在应用函数或运算符之前转换为text，从而去除character值中的任何尾随空格。

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

注意

字符串连接运算符 (||) 将接受非字符串输入，只要至少一个输入为字符串类型，如表 9.9中所示。对于其他情况，可以插入显式强制转换为text，以接受非字符串输入。

表 9.9。SQL字符串函数和运算符

函数/运算符

说明

示例

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

返回字符串中的位数（octet_length 的 8 倍）。

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

通过在 string 前面添加字符 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 ( 文本 ) → 整数

返回字符串中的字节数。

octet_length('josé') → 5（如果服务器编码为 UTF8）

octet_length ( 字符 ) → 整数

返回字符串中的字节数。由于此版本的函数直接接受类型 字符，因此它不会去除尾随空格。

octet_length('abc '::character(4)) → 4

overlay ( 字符串 文本 PLACING 新子字符串 文本 FROM 开始 整数 [ FOR 计数 整数 ] ) → 文本

用 新子字符串 替换从 开始'th 字符开始并延伸 计数 个字符的 字符串 子字符串。如果省略 计数，则它默认为 新子字符串 的长度。

overlay('Txxxxas' placing 'hom' from 2 for 4) → Thomas

position ( 子字符串 文本 IN 字符串 文本 ) → 整数

返回指定 子字符串 在 字符串 中的第一个起始索引，如果不存在，则返回零。

position('om' in 'Thomas') → 3

rpad ( 字符串 文本, 长度 整数 [, 填充 文本 ] ) → 文本

通过附加字符 填充（默认情况下为空格）将 字符串 扩展到长度 长度。如果 字符串 已长于 长度，则将其截断。

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，则从第 start 个字符开始，如果指定了 count，则在 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 正则表达式匹配的第一个子字符串；请参见 第 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

提取与 正则表达式匹配的第一个子字符串；请参见 第 9.7.2 节。第一种形式已从 SQL:2003 开始指定；第二种形式仅在 SQL:1999 中，应被视为已过时。

substring('Thomas' similar '%#"o_a#"_' escape '#') → 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

其他字符串操作函数和运算符可用，并列于表 9.10中。（其中一些在内部用于实现表 9.9中列出的SQL标准字符串函数。）还有模式匹配运算符，在第 9.7 节中进行了描述，以及全文搜索运算符，在第 12 章中进行了描述。

表 9.10。其他字符串函数和运算符

函数/运算符

说明

示例

text ^@ text → boolean

如果第一个字符串以第二个字符串开头，则返回 true（等同于 starts_with() 函数）。

'alphabet' ^@ 'alph' → t

ascii ( text ) → integer

返回参数第一个字符的数字代码。在 编码中，返回字符的 Unicode 代码点。在其他多字节编码中，参数必须是 字符。

ascii('x') → 120

chr ( integer ) → text

返回具有给定代码的字符。在 编码中，参数被视为 Unicode 代码点。在其他多字节编码中，参数必须指定 字符。不允许使用 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

根据格式字符串设置参数格式；请参见 第 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| 个字符之外的所有字符。

left('abcde', 2) → ab

length ( text ) → integer

返回字符串中的字符数。

length('jose') → 4

md5 ( text ) → text

计算参数的 MD5 哈希，结果以十六进制形式写入。

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

返回给定的字符串，该字符串经过适当的引用，可用于 语句字符串中的标识符。仅在必要时才添加引号（即，如果字符串包含非标识符字符或会被折叠大小写）。嵌入式引号被正确地加倍。另请参见示例 43.1。

quote_ident('Foo bar') → "Foo bar"

quote_literal ( text ) → text

返回给定的字符串，该字符串经过适当的引用，可用于 语句字符串中的字符串文字。嵌入式单引号和反斜杠被正确地加倍。请注意，quote_literal 在空输入时返回 null；如果参数可能是 null，quote_nullable 通常更合适。另请参见示例 43.1。

quote_literal(E'O\'Reilly') → 'O''Reilly'

quote_literal ( anyelement ) → text

将给定的值转换为文本，然后将其作为文字引用。嵌入式单引号和反斜杠被正确地加倍。

quote_literal(42.5) → '42.5'

quote_nullable ( text ) → text

返回给定字符串，将其适当引用以用作 语句字符串中的字符串文字；或者，如果参数为 null，则返回 NULL。嵌入的单引号和反斜杠会正确加倍。另请参见 示例 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 中匹配的次数；请参见 第 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 次匹配出现的位置，如果没有此类匹配，则返回零；请参见 第 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 中出现；请参阅 第 9.7.3 节。

regexp_like('Hello World', 'world$', 'i') → t

regexp_match ( string text, pattern text [, flags text ] ) → text[]

返回 POSIX 正则表达式 pattern 的第一个匹配项中 string 的子字符串；请参阅 第 9.7.3 节。

regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}

regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]

返回 POSIX 正则表达式 pattern 的第一个匹配项中 string 的子字符串，或者如果使用 g 标志，则返回所有此类匹配项中的子字符串；请参阅 第 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 标志，则替换所有匹配项；请参阅 第 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 为零，则替换所有匹配项；请参阅 第 9.7.3 节。

regexp_replace('Thomas', '.', 'X', 3, 2) → ThoXas

regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]

使用 POSIX 正则表达式作为分隔符拆分 string，生成结果数组；请参阅 第 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，生成结果集；请参阅 第 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；请参阅 第 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

用子字符串 to 替换 string 中所有出现的子字符串 from。

replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef

reverse ( text ) → text

反转字符串中的字符顺序。

reverse('abcde') → edcba

right ( string text, n integer ) → text

返回字符串中最后的 n 个字符，或者当 n 为负数时，返回除了前 |n| 个字符之外的所有字符。

right('abcde', 2) → de

split_part ( string text, delimiter text, n integer ) → text

在 delimiter 出现的次数处拆分 string，并返回第 n 个字段（从 1 开始计数），或者当 n 为负数时，返回从最后开始的第 |n| 个字段。

split_part('abc~@~def~@~ghi', '~@~', 2) → def

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

在 delimiter 出现的次数处拆分 string，并将结果字段作为 text 行的集合返回。如果 delimiter 为 NULL，则 string 中的每个字符都将成为结果的单独一行。如果 delimiter 为空字符串，则 string 将被视为单个字段。如果提供了 null_string 且不为 NULL，则与该字符串匹配的字段将被 NULL 替换。

string_to_table('xx~^~yy~^~zz', '~^~', 'yy') →

 xx
 NULL
 zz

strpos ( string text, substring text ) → integer

返回指定 substring 在 string 中首次出现的起始索引，如果不存在，则返回零。（与 position(substring in string) 相同，但请注意参数顺序相反。）

strpos('high', 'ig') → 2

substr ( string text, start integer [, count integer ] ) → text

从 string 的第 start 个字符开始提取子字符串，如果指定了 count，则扩展 count 个字符。（与 substring(string from start for count) 相同。）

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 从其他编码（可通过名称或数字识别）转换为 。如果省略 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 ) → text

评估参数中转义的 Unicode 字符。Unicode 字符可指定为 \XXXX（4 个十六进制数字）、\+XXXXXX（6 个十六进制数字）、\uXXXX（4 个十六进制数字）或 \UXXXXXXXX（8 个十六进制数字）。要指定反斜杠，请写两个反斜杠。所有其他字符都按字面意思接受。

如果服务器编码不是 UTF-8，则由这些转义序列之一标识的 Unicode 代码点将转换为实际的服务器编码；如果不可能，则会报告错误。

此函数提供了一个（非标准）的替代方案，用于带 Unicode 转义字符的字符串常量（请参见 第 4.1.2.3 节）。

unistr('d\0061t\+000061') → data

unistr('d\u0061t\U00000061') → data

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

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

9.4.1.format# ​

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

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

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

格式说明符以%字符开头，格式如下

%[position][flags][width]type

其中组件字段为

位置（可选）

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

标志（可选）

控制格式说明符输出格式的其他选项。目前唯一支持的标志是减号 (-)，它将导致格式说明符的输出左对齐。除非还指定了 宽度 字段，否则此标志无效。

宽度（可选）

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

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

类型（必需）

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

  • s 将参数值格式化为简单字符串。空值将被视为一个空字符串。

  • I 将参数值视为 SQL 标识符，必要时用双引号引起来。该值为空（等效于 quote_ident）是错误的。

  • L 将参数值作为 SQL 字面量引用。空值将显示为字符串 NULL，不带引号（等效于 quote_nullable）。

除了上面描述的格式说明符之外，特殊序列%%可用于输出一个文本%字符。

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

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 语句特别有用。请参阅示例 43.1。