字符串函数和操作符

字符串函数和操作符¶

格式检查及操作函数¶

本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型character、character varying和text的值。除非另外说明，所有下面列出的函数都可以处理这些类型，不过要小心的是，在使用character类型的时候，它有自动填充空白的潜在影响。有些函数还可以处理位串类型。

SQL定义了一些字符串函数，它们使用关键字，而不是逗号来分隔参数。详情请见 SQL字符串函数和操作符，SeaboxSQL也提供了这些函数使用正常函数调用语法的版本（见其他字符串函数）。

注意

字符串串接操作符（||）接受非字符串输入，只要至少一个输入是一种字符串类型，如SQL字符串函数和操作符所示。对于其他情况，可以为text插入一个显式强制转换。

`||`¶

语法

string || string, string || non-string/non-string || string

返回类型

text

描述

字符串拼接或字符串与非字符串拼接

示例

``` sql seaboxsql=# select 'Seabox' || 'SQL'; ?column?

SeaboxSQL (1 row)

seaboxsql=# select 'Value: ' || 42; ?column?

Value: 42 (1 row) ```

`bit_length()`¶

语法

bit_length(string)

返回类型

int

描述

串中的位数

示例

注意中文长度

seaboxsql=# select bit_length('jose');
 bit_length 
------------
         32
(1 row)
seaboxsql=# select bit_length('海盒');
 bit_length 
------------
         48
(1 row)

`char_length()/character_length()`¶

语法

char_length(string)/character_length(string)

返回类型

int

描述

字符长度

示例

注意中文长度

seaboxsql=# select char_length('jose');
 char_length 
-------------
           4
(1 row)

seaboxsql=# select char_length('海盒');
 char_length 
-------------
           2
(1 row)

`lower()`¶

语法: lower(string)
返回类型: text
描述: 将字符串转换为小写形式
示例: ``` sql seaboxsql=# select lower('TOM'); lower
tom (1 row) ```

`octet_length()`¶

语法

octet_length(string)

返回类型

int

描述

串中的字节数

示例

注意中文长度

seaboxsql=# select octet_length('jose');
 octet_length 
--------------
            4
(1 row)

seaboxsql=# select octet_length('海盒');
 octet_length 
--------------
            6
(1 row)

overlay¶

语法

overlay(string placing string from int [for int])

返回类型

text

描述

替换子串, 第一个参数为被替换的对象字符串；第二个参数为使用替换的子字符串；第三个参数为被替换字符串中的起始替换位置；第四个参数为被替换掉的子串长度，如未指定则长度与参数2子字符串一致。

示例

``` sql seaboxsql=# select overlay('Txxxxas' placing 'hom' from 2 for 4); overlay

Thomas (1 row)

seaboxsql=# select overlay('Txxxxas' placing 'hom' from 2 ); overlay

Thomxas (1 row) ```

`position()`¶

语法

position(substring in string)

返回类型

int

描述

定位指定子串的起始位置

示例

``` sql seaboxsql=# select position('om' in 'Thomas'); position

(1 row) ```

`substring()`¶

语法

sql
substring(string [from int] [for int])
substring(string from pattern)
substring(string from pattern for escap)

返回类型

text

描述

提取子串，第一种语法为指定起始位置及长度截取；第二种语法为使用POSIX正则表达式匹配截取；第三种语法为使用SQL正则表达式截取匹配子串

示例

``` sql seaboxsql=# select substring('Thomas' from 2 for 3); substring

hom (1 row)

seaboxsql=# select substring('Thomas' from '…$'); substring

mas (1 row)

seaboxsql=# select substring('Thomas' from '%#"o_a#"_' for '#'); substring

oma (1 row) ```

`trim()`¶

语法

trim([leading | trailing | both] [*characters*] from string), trim([leading \| trailing \| both] [from] *string* [, *characters*])

返回类型

text

描述

从string的开头、结尾或者两端（both是默认值）移除只包含characters（默认是一个空格）中字符的最长字符串; 第二种语法为非标准用法

示例

``` sql seaboxsql=# select trim(both 'xyz' from 'yxTomxx'); btrim

Tom (1 row)

seaboxsql=# select trim(both from 'xTomxx', 'x'); btrim

Tom (1 row) ```

`upper()`¶

语法: upper(string)
返回类型: text
描述: 将字符串转换成大写形式
示例: ``` sql seaboxsql=# select upper('tom'); upper
TOM (1 row) ```

其他字符串函数¶

还有额外的串操作函数可以用，它们在下面其他字符串函数中列出。它们有些在内部用于实现SQL字符串函数和操作符列出的SQL标准字符串函数。

`ascii()`¶

语法: ascii(string)
返回类型: int
描述: 参数第一个字符的ASCII代码。对于UTF8返回该字符的Unicode代码点。对于其他多字节编码，该参数必须是一个ASCII字符。
示例: ``` sql seaboxsql=# select ascii('x'); ascii
120 (1 row) ```

`btrim()`¶

语法: btrim(*string* text [, *characters* text])
返回类型: text
描述: 从 string 的开头或结尾删除最长的只包含 characters（默认是一个空格）的串
示例: ``` sql seaboxsql=# select btrim('xyxtrimyyx', 'xyz'); btrim
trim (1 row) ```

`chr()`¶

语法: chr(int)
返回类型: text
描述: 给定代码的字符。对于UTF8该参数被视作一个Unicode代码点。对于其他多字节编码该参数必须指定一个ASCII字符。NULL (0) 字符不被允许，因为文本数据类型不能存储这种字节。
示例: ``` sql seaboxsql=# select chr(65); chr
A (1 row) ```

`concat()`¶

语法: concat(*str* "any" [, *str* "any" [, ...] ])
返回类型: text
描述: 串接所有参数的文本表示。NULL 参数被忽略。
示例: ``` sql seaboxsql=# select concat('abcde', 2, NULL, 22); concat
abcde222 (1 row) ```

`concat_ws()`¶

语法: concat_ws(*sep* text, *str* "any" [, *str* "any" [, ...] ])
返回类型: text
描述: 将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略。
示例: ``` sql seaboxsql=# select concat_ws(',', 'abcde', 2, NULL, 22); concat_ws
abcde,2,22 (1 row) ```

`convert()`¶

语法: convert(string bytea, src_encoding name,dest_encoding name)
返回类型: bytea
描述: 将字符串转换为 dest_encoding 。原始编码由 src_encoding 指定。string 在这个编码中必须可用。转换可以使用CREATE CONVERSION定义。也有一些预定义的转换。
示例: ``` sql seaboxsql=# select convert('text_in_utf8', 'UTF8', 'LATIN1'); convert
x746578745f696e5f75746638 (1 row) ```

`convert_from()`¶

语法: convert_from(string bytea, src_encoding name)
返回类型: text
描述: 将字符串转换为数据库编码。原始编码由 src_encoding指定。string 在这个编码中必须可用。
示例: ``` sql seaboxsql=# select convert_from('text_in_utf8', 'UTF8'); convert_from
text_in_utf8 (1 row) ```

convert_to¶

语法: convert_to(string text, dest_encoding name)
返回类型: bytea
描述: 将字符串转换为 dest_encoding 。
示例: ``` sql seaboxsql=# select convert_to('some text', 'UTF8'); convert_to
x736f6d652074657874 (1 row) ```

`decode()`¶

语法: decode(string text, format text)
返回类型: bytea
描述: 从 string 中的文本表达解码二进制数据。 format 的选项和 encode 中的一样。
示例: ``` sql seaboxsql=# select decode('MTIzAAE=', 'base64'); decode
x3132330001 (1 row) ```

`encode()`¶

语法: encode(data bytea, format text)
返回类型: text
描述: 将二进制数据编码成一个文本表达。支持的格式有：base64、hex、escape。escape将零字节和高位组字节转换为八进制序列（\nnn ）和双写的反斜线。
示例: ``` sql seaboxsql=# select encode('123000001', 'base64'); encode
MTIzAAE= (1 row) ```

`format()`¶

语法

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

返回类型

text

描述

根据一个格式字符串格式化参数。该函数和C函数sprintf相似。

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）。

除了以上所述的格式说明符之外，要输出一个文字形式的%字符，可以使用特殊序列%%。

示例

``` sql seaboxsql=# select format('Hello %s, %1$s', 'World'); format

Hello World, World (1 row)

seaboxsql=# SELECT format('Hello %s', 'World'); format

Hello World (1 row)

seaboxsql=# SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three'); format

Testing one, two, three, % (1 row)

seaboxsql=# SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O'Reilly'); format

INSERT INTO "Foo bar" VALUES('O''Reilly') (1 row)

seaboxsql=# SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:Program Files'); format

INSERT INTO locations VALUES(E'C:\Program Files') (1 row)

– 使用 width 域和-标志的例子： seaboxsql=# SELECT format('|%10s|', 'foo'); format

| foo| (1 row)

seaboxsql=# SELECT format('|%-10s|', 'foo'); format

|foo | (1 row)

seaboxsql=# SELECT format('|%*s|', 10, 'foo'); format

| foo| (1 row)

seaboxsql=# SELECT format('|%*s|', -10, 'foo'); format

|foo | (1 row)

seaboxsql=# SELECT format('|%-*s|', 10, 'foo'); format

|foo | (1 row)

seaboxsql=# SELECT format('|%-*s|', -10, 'foo'); format

|foo | (1 row)

– 使用position域的例子： seaboxsql=# SELECT format('Testing %3 $s, %2$ s, %1$s', 'one', 'two', 'three'); format

Testing three, two, one (1 row)

seaboxsql=# SELECT format('|%*2$s|', 'foo', 10, 'bar'); format

| bar| (1 row)

seaboxsql=# SELECT format('|%1 $*2$ s|', 'foo', 10, 'bar'); format

| foo| (1 row)

– format函数不要求所有函数参数都被用在格式字符串中。 seaboxsql=# SELECT format('Testing %3 $s, %2$ s, %s', 'one', 'two', 'three'); format

Testing three, two, three (1 row) ```

`initcap()`¶

语法: initcap(string)
返回类型: text
描述: 将每一个词的第一个字母转换为大写形式并把剩下的字母转换为小写形式。词是由非字母数字字符分隔的字母数字字符的序列。
示例: ``` sql seaboxsql=# select initcap('hi THOMAS'); initcap
Hi Thomas (1 row) ```

`left()`¶

语法: left(str text, n int)
返回类型: text
描述: 返回字符串中的前 n 个字符。当 n 为负时，将返回除了最后| n |个字符之外的所有字符。
示例: ``` sql seaboxsql=# select left('abcde', 2); left
ab (1 row) ```

`length()`¶

语法

length(string);length(string bytea, encoding name )

返回类型

int

描述

string 中的字符数; 参数为2个时，返回string 在给定编码中的字符数，string 必须在这个编码中有效。

示例

``` sql seaboxsql=# select length('jose'); length

4 (1 row)

seaboxsql=# select length('海盒'); length

(1 row)

seaboxsql=# select length('jose', 'UTF8'); length

(1 row)

seaboxsql=# select length('海盒', 'UTF8'); length

(1 row) ```

`lpad()`¶

语法: lpad(string text, length int [, *fil8l text])
返回类型: text
描述: 将 string 通过前置字符 fill（默认是一个空格）填充到长度 length 。如果 string 已经长于 length ，则它被（从右边）截断。
示例: ``` sql seaboxsql=# select lpad('hi', 5, 'xy'); lpad
xyxhi (1 row) ```

`ltrim()`¶

语法: ltrim( string text [, characters text])
返回类型: text
描述: 从 string 的开头删除最长的只包含 characters（默认是一个空格）的串
示例: ``` sql seaboxsql=# select ltrim('zzzytest', 'xyz'); ltrim
test (1 row) ```

`md5()`¶

语法: md5(string)
返回类型: text
描述: 计算 string 的 MD5 哈希，返回十六进制的结果
示例: ``` sql seaboxsql=# select md5('abc'); md5
900150983cd24fb0d6963f7d28e17f72 (1 row) ```

`parse_ident()`¶

语法: parse_ident(qualified_identifier text [,strictmode boolean DEFAULT true ] )
返回类型: text[]
描述: 把 qualified_identifier 分成一个标识符数组，移除单个标识符上的任何引号。默认情况下，最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false，那么这一类多余的字符会被忽略（这种行为对于解析函数之类的对象名称有用）。注意这个函数不会截断超长标识符。如果想要进行截断，可以把结果转换成name[]。
示例: ``` sql seaboxsql=# select parse_ident('"SomeSchema".someTable'); parse_ident
{SomeSchema,sometable} (1 row) ```

`pg_client_encoding()`¶

语法: pg_client_encoding()
返回类型: name
描述: 当前的客户端编码名字
示例: ``` sql seaboxsql=# select pg_client_encoding(); pg_client_encoding
UTF8 (1 row) ```

`quote_ident()`¶

语法: quote_ident( string text)
返回类型: text
描述: 将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个标识符。只有需要时才会加上引号（即，如果字符串包含非标识符字符或可能是大小写折叠的）。嵌入的引号会被正确地双写。
示例: ``` sql seaboxsql=# select quote_ident('Foo bar'); quote_ident
"Foo bar" (1 row) ```

`quote_literal()`¶

语法

quote_literal(string text), quote_literal(value anyelement)

返回类型

text

描述

将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空；如果参数可能为空，quote_nullable通常更合适。当参数为其他类型时，强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写。

示例

``` sql seaboxsql=# select quote_literal(E'O'Reilly'); quote_literal

'O''Reilly' (1 row)

seaboxsql=# select quote_literal(42.5); quote_literal

'42.5' (1 row) ```

`quote_nullable()`¶

语法

quote_nullable(string text), quote_nullable(value anyelement)

返回类型

text

描述

将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字；或者，如果参数为空，返回NULL。嵌入的引号会被正确地双写; 参数类型为其他数据类型时，强迫给定值为文本并且接着将它用引号包围作为一个文本；或者，如果参数为空，返回NULL。嵌入的单引号和反斜线被正确的双写。

示例

``` sql seaboxsql=# select quote_nullable(NULL); quote_nullable

NULL (1 row)

seaboxsql=# select quote_nullable(42.5); quote_nullable

'42.5' (1 row) ```

`regexp_match()`¶

语法: regexp_match(string text, pattern text [,flags text])
返回类型: text[]
描述: 返回一个POSIX正则表达式与 string 的第一个匹配得到的子串。
示例: ``` sql seaboxsql=# select regexp_match('foobarbequebaz', '(bar)(beque)'); regexp_match
{bar,beque} (1 row) ```

`regexp_matches()`¶

语法: regexp_matches(string text, pattern text [,flags text])
返回类型: setof text[]
描述: 返回一个POSIX正则表达式与string匹配得到的子串。
示例: ``` sql seaboxsql=# select regexp_matches('foobarbequebaz', 'ba.', 'g'); regexp_matches
{bar} {baz} (2 rows) ```

`regexp_replace()`¶

语法: regexp_replace(string text, pattern text [,flags text])
返回类型: text
描述: 替换匹配一个POSIX正则表达式的子串。
示例: ``` sql seaboxsql=# select regexp_replace('Thomas', '.[mN]a.', 'M'); regexp_replace
ThM (1 row) ```

`regexp_split_to_array()`¶

语法: regexp_split_to_array(string text, pattern text [,flags text])
返回类型: text[]
描述: 使用一个POSIX正则表达式作为分隔符划分 string 。
示例: ``` sql seaboxsql=# select regexp_split_to_array('hello world', 's+'); regexp_split_to_array
{hello,world} (1 row) ```

`regexp_split_to_table()`¶

语法: regexp_split_to_table(string text, pattern text [,flags text])
返回类型: setof text
描述: 使用一个POSIX正则表达式作为分隔符划分 string。
示例: ``` sql seaboxsql=# select regexp_split_to_table('hello world', 's+'); regexp_split_to_table
hello world (2 rows) ```

`repeat()`¶

语法: repeat(string text, number int)
返回类型: text
描述: 重复 string 指定的 number 次
示例: ``` sql seaboxsql=# select repeat('seabox', 4); repeat
seaboxseaboxseaboxseabox (1 row) ```

`replace()`¶

语法: replace(string text, from text, to text)
返回类型: text
描述: 将 string 中出现的所有子串 from 替换为子串 to
示例: ``` sql seaboxsql=# select replace('abcdefabcdefdc', 'cd', 'XX'); replace
abXXefabXXefdc (1 row) ```

`reverse()`¶

语法: reverse(str)
返回类型: text
描述: 返回反转的字符串。
示例: ``` sql seaboxsql=# select reverse('abcde'); reverse
edcba (1 row) ```

`right()`¶

语法: right(str text, n int)
返回类型: text
描述: 返回字符串中的最后 n 个字符。如果 n 为负，返回除最前面的|n|个字符外的所有字符。
示例: ``` sql seaboxsql=# select right('abcde', 2); right
de (1 row) ```

`rpad()`¶

语法: rpad(string text, length int [, fill text])
返回类型: text
描述: 将 string 通过增加字符 fill（默认为一个空格）填充到长度 length。如果 string 已经长于 length 则它会被截断。
示例: ``` sql seaboxsql=# select rpad('hi', 5, 'xy'); rpad
hixyx (1 row) ```

`rtrim()`¶

语法: rtrim(string text [, characters text])
返回类型: text
描述: 从string的结尾删除最长的只包含characters（默认是一个空格）的串
示例: ``` sql seaboxsql=# select rtrim('testxxzx', 'xyz'); rtrim
test (1 row) ```

`split_part()`¶

语法: split_part(string text, delimiter text,field int)
返回类型: text
描述: 按 delimiter 划分 string 并返回给定域（从1开始计算）
示例: ``` sql seaboxsql=# select split_part('abc_@def_@ghi', '_@', 2); split_part
def (1 row) ```

`strpos()`¶

语法: strpos(string, substring)
返回类型: int
描述: 指定子串的位置（和position(substring in string)相同，但是注意相反的参数顺序）
示例: ``` sql seaboxsql=# select strpos('high', 'ig'); strpos

2 (1 row) ```

`substr()`¶

语法: substr(string, from [, count])
返回类型: text
描述: 提取子串（与substring (string from from for count)相同）
示例: ``` sql seaboxsql=# select substr('alphabet', 3, 2); substr
ph (1 row) ```

`starts_with()`¶

语法: starts_with(string, prefix)
返回类型: bool
描述: 如果 string 以 prefix 开始则返回真。
示例: ``` sql seaboxsql=# select starts_with('alphabet', 'alph'); starts_with
t (1 row) ```

`to_ascii()`¶

语法: to_ascii(string text [, encoding text])
返回类型: text
描述: 将 string 从另一个编码转换到ASCII（只支持从LATIN1、LATIN2、LATIN9和WIN1250编码的转换）
示例: ``` sql seaboxsql=# select to_ascii('Karel','latin1'); to_ascii
Karel (1 row) ```

`to_hex()`¶

语法: to_hex(number int or bigint)
返回类型: text
描述: 将 number 转换到它等效的十六进制表示
示例: ``` sql seaboxsql=# select to_hex(2147483647); to_hex
7fffffff (1 row) ```

translate()¶

语法

translate(string text, from text, to text)

返回类型

text

描述

string 中任何匹配 from 集合中一个字符的字符会被替换成 to 集合中的相应字符。如果 from 比 to 长，from 中的额外字符会被删除。

示例

``` sql seaboxsql=# select translate('12345', '143', 'ax'); translate

a2x5 (1 row)

seaboxsql=# select translate('12345', '14', 'axt'); translate

a23x5 (1 row) ```

字符串函数和操作符