- 9.4.1.
format
本节描述了用于检查和操作字符串值的函数和操作符。 在这个环境中的串包括所有类型character
、character varying
和text
的值。 除非特别说明,这些函数和操作符声明为接受并返回text
类型。 他们将互换接受character varying
参数。 在应用函数或操作符之前character
类型的值将被转换为text
,结果删除character
值中的任何末尾空格。
SQL定义了一些字符串函数,它们使用关键字,而不是逗号来分隔参数。详情请见表 9.9,PostgreSQL也提供了这些函数使用正常函数调用语法的版本(见表 9.10)。
注意
由于存在从那些数据类型到text
的隐式强制措施,在PostgreSQL 8.3之前,这些函数也可以接受多种非字符串数据类型。这些强制措施在目前的版本中已经被删除,因为它们常常导致令人惊讶的行为。不过,字符串串接操作符(||
)仍然接受非字符串输入,只要至少一个输入是一种字符串类型,如表 9.9所示。对于其他情况,如果你需要复制之前的行为,可以为text
插入一个显式强制措施。
表 9.9. SQL字符串函数和操作符
函数/操作符 描述 例子 |
---|
text || text → text
连接两个字符串。 'Post' || 'greSQL' → PostgreSQL
|
text || anynonarray → text
anynonarray || text → text
将非字符串输入转换为文本,然后将两个字符串串联在一起。 (非字符串输入不能为数组类型,因为这将在|| 操作符的数组中造成歧义。如果你想连接一个数组的文本相等的,请显式地将其转换为text 。) 'Value: ' || 42 → Value: 42
|
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
|
normalize ( text [, form ] ) → text
将字符串转换为指定的Unicode规范化形式。 可选的form 关键字指定了如下形式:NFC (the default),NFD , NFKC ,或NFKD 。 该函数只能在服务器编码为UTF8 时使用。 normalize(U&'\0061\0308bc', NFC) → U&'\00E4bc'
|
octet_length ( text ) → integer
返回字符串的字节数。 octet_length('josé') → 5 (if server encoding is 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 字符开始的子串,并用newsubstring 扩展到count 字符。 如果省略了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
|
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正则表达式的子字符串; 参见 第 9.7.3 节。 substring('Thomas' from '...$') → mas
|
substring ( string text FROM pattern text FOR escape text ) → text
提取匹配 SQL 正则表达式的字串;参见 第 9.7.2 节。 substring('Thomas' from '%#"o_a#"_' for '#') → 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.10. 其他字符串函数
函数 描述 例子 |
---|
ascii ( text ) → integer
返回参数的第一个字符的数字代码。在UTF8编码中,返回该字符的Unicode代码点。 在其他多字节编码中,该参数必须是一个ASCII字符。 ascii('x') → 120
|
btrim ( string text [, characters text ] ) → text
从string 的开头或结尾删除最长的只包含characters (默认是一个空格)的字符串 btrim('xyxtrimyyx', 'xyz') → trim
|
chr ( integer ) → text
返回给定代码的字符。在UTF8编码中该参数被视作一个Unicode代码点。 在其他多字节编码中该参数必须指定一个ASCII字符。 chr(0) 字符不被允许,因为文本数据类型不能存储这种字符。 chr(65) → A
|
concat ( val1 "any" [, val2 "any" [, ...] ] ) → text
连接所有参数的文本表示。空参数被忽略。 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
|
lpad ( string text , length integer [, fill text ] ) → text
将string 扩展为长度length ,通过前置字符fill (默认空格)。 如果string 已经超过length 那么它将被截断(在右侧)。 lpad('hi', 5, 'xy') → xyxhi
|
ltrim ( string text [, characters text ] ) → text
从string 开始删除包含characters (默认空格)中仅包含字符的最长字符串。 ltrim('zzzytest', 'xyz') → test
|
md5 ( text ) → text
计算参数的 MD5 hash ,结果以十六进制形式写入。 md5('abc') → 900150983cd24fb0d6963f7d28e17f72
|
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语句字符串中的标识符。 只有在必要的情况下才添加引号(例如,如果字符串包含非标识符字符或将被大小写折叠)。 嵌入的引号被适当地加双引号。参见例 42.1。 quote_ident('Foo bar') → "Foo bar"
|
quote_literal ( text ) → text
返回在SQL语句字符串中适当引用的给定字符串,用作字符串文字使用。 嵌入式单引号和反斜线适当的翻倍(转双引号或双斜线)。 请注意,quote_literal 返回无效输入;如果这个参数可能为空,quote_nullable 通常更合适。另请参阅例 42.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 。 内嵌的单引号和反斜杠被适当地翻倍。参见例 42.1。 quote_nullable(NULL) → NULL
|
quote_nullable ( anyelement ) → text
将给定值转换为文本,然后将其作为字面量引用;或者,如果参数为null,则返回NULL 。 内嵌的单引号和反斜杠被适当地翻倍。 quote_nullable(42.5) → '42.5'
|
regexp_match ( string text , pattern text [, flags text ] ) → text[]
返回从POSIX正则表达式到string 的第一个匹配中捕获的子字符串;参见第 9.7.3 节。 regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}
|
regexp_matches ( string text , pattern text [, flags text ] ) → setof text[]
返回通过将POSIX正则表达式与string 匹配而捕获的子字符串;参见第 9.7.3 节。 regexp_matches('foobarbequebaz', 'ba.', 'g') →
{bar} {baz} |
regexp_replace ( string text , pattern text , replacement text [, flags text ] ) → text
替换匹配POSIX正则表达式的子字符串;参见 第 9.7.3 节。 regexp_replace('Thomas', '.[mN]a.', 'M') → ThM
|
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 |
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 |字符之外的所有字符。 right('abcde', 2) → de
|
rpad ( string text , length integer [, fill text ] ) ) → text
扩展 string 到长度 length ,通过追加fill 字符(默认为空格). 如果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 , n integer ) → text
在delimiter 出现时拆分string ,并且返回第n 个字段(从一计数)。 split_part('abc~@~def~@~ghi', '~@~', 2) → def
|
strpos ( string text , substring text ) → integer
返回在string 中指定的substring 的起始索引,如果不存在则为零。 (与(substring 在 string 中的)位置 相同,但是请注意反转的参数顺序) strpos('high', 'ig') → 2
|
substr ( string text , start integer [, count integer ] ) → text
提取string 从start 字符开始的子字符串,并扩展count 字符,如果指定了的话。 (与 子字符串(string 从 start 开始计数 count ) 相同。) substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph
|
starts_with ( string text , prefix text ) → boolean
如果 string 以 prefix 开始就返回真。 starts_with('alphabet', 'alph') → t
|
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
|
concat
、concat_ws
和format
函数是可变的,因此可以把要串接或格式化的值作为一个标记了VARIADIC
关键字的数组进行传递(见第 37.5.5 节)。 数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为 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
其中的各组件域是:
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
格式说明符特别有用。参见例 42.1。