字符串函数和操作符
字符串函数和操作符#
字符串函数和操作符
本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型character、character varying和text的值。除非另外说明，所有下面列出的函数都可以处理这些类型，不过要小心的是，在使用character类型的时候，
它有自动填充空白的潜在影响。有些函数还可以处理位串类型。
SQL定义了一些字符串函数，它们使用关键字，而不是逗号来分隔参数。详情请见表，PostgreSQL也提供了这些函数使用正常函数调用语法的版本（见表）。
备注
注意
由于存在从那些数据类型到text的隐式强制措施，在PostgreSQL
8.3之前，这些函数也可以接受多种非字符串数据类型。这些强制措施在目前的版本中已经被删除，因为它们常常导致令人惊讶的行为。不过，字符串串接操作符（||）仍然接受非字符串输入，只要至少一个输入是一种字符串类型，如表所示。对于其他情况，如果你需要复制之前的行为，可以为text插入一个显式强制措施。
表 SQL字符串函数和操作符
函数 |
返回类型 |
描述 |
例子 |
结果 |
|---|---|---|---|---|
string || string |
text |
串接 |
|
P ostgreSQL |
string || no n-string or n on-string || string |
text |
使用一 个非字符串 输入的串接 |
‘Value : ‘ || 42 |
Value: 42 |
bit_lengt h(string ) |
int |
串中的位数 |
bit_lengt h(‘jose’) |
32 |
h(string ) or char acter_lengt h(string ) |
int |
串中字符数 |
char_lengt h(‘jose’) |
4 |
lowe r(string ) |
text |
将 字符串转换 为小写形式 |
low er(‘TOM’) |
tom |
octet_lengt h(string ) |
int |
串 中的字节数 |
octet_lengt h(‘jose’) |
4 |
overl ay(string placing string from int [for int ]) |
text |
替换子串 |
ove rlay(‘Txxxx as’ placing ‘hom’ from 2 for 4) |
Thomas |
position( substring in
|
int |
定 位指定子串 |
positi on(‘om’ in ‘Thomas’) |
3 |
substri ng(string [from int ] [for int ]) |
text |
提取子串 |
s ubstring(‘T homas’ from 2 for 3) |
hom |
substri ng(string from pattern ) |
text |
提取 匹配POSIX正 则表达式的 子串。模式 匹配详情见第 节 <functions -matching.h tml> __。 |
substring(’ Thomas’ fro m ‘…$’) |
mas |
substri ng(string from pattern for * escape ) |
text |
提 取匹配SQL正 则表达式的 子串。模式 匹配详情见第 节 <functions -matching.h tml> __。 |
su bstring(‘Th omas’ from ‘%#”o_a#”_’ for ‘#’) |
oma |
trim([leadi ng | traili ng | both] [ch aracters ] from string ) |
text |
从 string 的开头 、结尾或者 两端（ b oth 是默 认值）移除 只包含* characters （默认 是一个空格 ）中字符的 最长字符串 |
trim(both ‘ xyz’ from ‘ yxTomxx’) |
Tom |
trim([lea ding | trai ling | both] [ from] string [, cha racters ] ) |
text |
t rim() 的 非标准版本 |
xx’, ‘x’) |
Tom |
uppe r(string ) |
text |
将 字符串转换 成大写形式 |
upp er(‘tom’) |
TOM |
还有额外的串操作函数可以用，它们在表中列出。它们有些在内部用于实现表列出的SQL标准字符串函数。
表 其他字符串函数
函数 |
返回类型 |
描述 |
例子 |
结果 |
|---|---|---|---|---|
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 |
|
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 |
|
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定义。也有一些预定义的转换。可用的转换请见表。 |
convert(‘text_in_utf8’, ‘UTF8’, ‘LATIN1’) |
|
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’) |
用UTF8编码表达的some text |
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(‘123000001’, ‘base64’) |
MTIzAAE= |
format( formatstr text[, formatarg “any”[, …] ]) |
text |
根据一个格式字符串格式化参数。该函数和C函数sprintf相似。见节。 |
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 |
string 在给定 编码 中的字符数。 string 必须在这个编码中有效。 |
length(‘jose’, ‘UTF8’) |
4 |
lpad( string text, length int[, fill text]) |
text |
将 string 通过前置字符 fill （默认是一个空格）填充到长度 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 booleanDEFAULT true ] ) |
text[] |
把 qualified_identifier 分成一个标识符数组，移除单个标识符上的任何引号。默认情况下，最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false，那么这一类多余的字符会被忽略（这种行为对于解析函数之类的对象名称有用）。注意这个函数不会截断超长标识符。如果想要进行截断，可以把结果转换成name[]。 |
parse_ident(‘“SomeSchema”.someTable’) |
{SomeSchema,sometable} |
pg_client_encoding() |
name |
当前的客户端编码名字 |
pg_client_encoding() |
SQL_ASCII |
quote_ident( string text) |
text |
将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个标识符。只有需要时才会加上引号（即，如果字符串包含非标识符字符或可能是大小写折叠的）。嵌入的引号会被正确地双写。参见例。 |
quote_ident(‘Foo bar’) |
“Foo bar” |
quote_literal( string text) |
text |
将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空；如果参数可能为空，quote_nullable通常更合适。参见例。 |
quote_literal(E’O'Reilly’) |
‘O’’Reilly’ |
quote_literal( value anyelement) |
text |
强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写。 |
quote_literal(42.5) |
‘42.5’ |
quote_nullable( string text) |
text |
将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字；或者，如果参数为空，返回NULL。嵌入的引号会被正确地双写。请参见例。 |
quote_nullable(NULL) |
NULL |
quote_nullable( value anyelement) |
text |
强迫给定值为文本并且接着将它用引号包围作为一个文本；或者，如果参数为空，返回NULL。嵌入的单引号和反斜线被正确的双写。 |
quote_nullable(42.5) |
‘42.5’ |
regexp_match( string text, pattern text[, flags text]) |
text[] |
返回一个POSIX正则表达式与 string 的第一个匹配得到的子串。更多信息请见节。 |
regexp_match(‘foobarbequebaz’, ‘(bar)(beque)’) |
{bar,beque} |
regexp_matches( string text, pattern text[, flags text]) |
setof text[] |
返回一个POSIX正则表达式与 string 匹配得到的子串。更多信息请见节。 |
regexp_matches(‘foobarbequebaz’, ‘ba.’, ‘g’) |
|
regexp_replace( string text, pattern text, replacement text[, flags text]) |
text |
替换匹配一个POSIX正则表达式的子串。详见节。 |
regexp_replace(‘Thomas’, ‘.[mN]a.’, ‘M’) |
ThM |
regexp_split_to_array( string text, pattern text[, flags text]) |
text[] |
使用一个POSIX正则表达式作为分隔符划分 string 。详见节。 |
regexp_split_to_array(‘hello world’, ‘s+’) |
{hello,world} |
regexp_split_to_table( string text, pattern text[, flags text]) |
setof text |
使用一个POSIX正则表达式作为分隔符划分 string 。详见节。 |
regexp_split_to_table(‘hello world’, ‘s+’) |
|
repeat( string text, number int) |
text |
重复 string 指定的 number 次 |
repeat(‘Pg’, 4) |
PgPgPgPg |
replace( string text, from text, to text) |
text |
将 string 中出现的所有子串 from 替换为子串 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 |
将 string 通过增加字符 fill （默认为一个空格）填充到长度 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 )相同，但是注意相反的参数顺序） |
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 开始则返回真。 |
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 intorbigint) |
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关键字的数组进行传递。数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为
NULL，concat和concat_ws返回 NULL，但format把
NULL 当作一个零元素数组。
还可以参阅节中的string_agg。
表 内建转换
转换名 [a] |
源编码 |
目标编码 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
转换名遵循一
种标准命名模式：将全部非
字母数字字符替换为下划线
的源编码的官方名称，后面
跟上 |
format
函数format根据一个格式字符串产生格式化的输出，其形式类似于
C 函数sprintf。
format(formatstr text [, formatarg "any" [, ...] ])
formatstr是一个格式字符串，它指定了结果应该如何被格式化。格式字符串中的文本被直接复制到结果中，除了使用格式说明符的地方。格式说明符在字符串中扮演着占位符的角色，它定义后续的函数参数如何被格式化及插入到结果中。每一个formatarg参数会被根据其数据类型的常规输出规则转换为文本，并接着根据格式说明符被格式化和插入到结果字符串中。
格式说明符由一个%字符开始并且有这样的形式
%[position][flags][width]type
其中的各组件域是：
position（可选）一个形式为
n$的字符串，其中n是要打印的参数的索引。索引 1 表示formatstr之后的第一个参数。如果position被忽略，默认会使用序列中的下一个参数。flags（可选）控制格式说明符的输出如何被格式化的附加选项。当前唯一支持的标志是一个负号（
-），它将导致格式说明符的输出会被左对齐（left-justified）。除非width域也被指定，否者这个域不会产生任何效果。width（可选）指定用于显示格式说明符输出的最小字符数。输出将被在左部或右部（取决于
-标志）用空格填充以保证充满该宽度。太小的宽度设置不会导致输出被截断，但是会被简单地忽略。宽度可以使用下列形式之一指定：一个正整数；一个星号（*）表示使用下一个函数参数作为宽度；或者一个形式为*n$的字符串表示使用第n个函数参数作为宽度。如果宽度来自于一个函数参数，则参数在被格式说明符的值使用之前就被消耗掉了。如果宽度参数是负值，结果会在长度为
abs(width)的域中被左对齐（如果-标志被指定）。type（必需）格式转换的类型，用于产生格式说明符的输出。支持下面的类型：
s将参数值格式化为一个简单字符串。一个控制被视为一个空字符串。I将参数值视作 SQL 标识符，并在必要时用双写引号包围它。如果参数为空，将会是一个错误（等效于quote_ident）。L将参数值引用为 SQL 文字。一个空值将被显示为不带引号的字符串NULL（等效于quote_nullable）。
除了以上所述的格式说明符之外，要输出一个文字形式的%字符，可以使用特殊序列%%。
下面有一些基本的格式转换的例子：
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', '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格式说明符特别有用。参见例。