PolarDBPostgreSQL有哪些字符串函数和操作符_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本节描述了用于检查和操作字符串值的函数和操作符。

在这个环境中的串包括所有类型 character 、 character varying 和 text 的值。除非特别说明，这些函数和操作符声明为接受并返回 text 类型。他们将互换接受 character varying 参数。在应用函数或操作符之前 character 类型的值将被转换为 text ，结果删除 character 值中的任何末尾空格。

SQL 定义了一些字符串函数，它们使用关键字，而不是逗号来分隔参数。详情请见 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正则表达式的子字符串。

substring('Thomas' from '...$') → mas

substring ( string text FROM pattern text FOR escape text ) → text

提取匹配 SQL 正则表达式的字串。

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

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

根据格式字符串对参数进行格式化。这个函数类似于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语句字符串中的标识符。只有在必要的情况下才添加引号（例如，如果字符串包含非标识符字符或将被大小写折叠）。嵌入的引号被适当地加双引号。

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

quote_literal ( text ) → text

返回在SQL语句字符串中适当引用的给定字符串，用作字符串文字使用。嵌入式单引号和反斜线适当的翻倍（转双引号或双斜线）。请注意， quote_literal 返回无效输入；如果这个参数可能为空， quote_nullable 通常更合适。

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

quote_literal ( anyelement ) → text

将给定的值转换为文本，然后将其作为字面量引用。内嵌的单引号和反斜杠被适当地翻倍。

quote_literal(42.5) → '42.5'

quote_nullable ( text ) → text

返回在SQL语句字符串中适当引用的给定字符串文字;或者，如果参数为null，则返回 NULL 。内嵌的单引号和反斜杠被适当地翻倍。

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 的第一个匹配中捕获的子字符串。

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

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

    %[position][flags][width]type

    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')

    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       |

    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|

    SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
    结果：Testing three, two, three

NLS_UPPER(char [, 'nlsparam' ])

参数	说明
char	指定要转换的字符串。字符串类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。

select NLS_UPPER('aaa BBB CcC') FROM DUAL;
  nls_upper  
-------------
 AAA BBB CCC

NLSSORT(char [, 'nlsparam' ])

参数	说明
char	指定要排序的字符串。字符串类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。
nlsparam	指定返回字符串的排序方式，为可选项。

CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('ccc');
INSERT INTO test VALUES ('bbb');
INSERT INTO test VALUES ('aaa');
SELECT * FROM test ORDER BY name;
-----------
 Gaardiner
 Gaasten
 Gaberd
SELECT * FROM test ORDER BY NLSSORT(name, 'NLS_SORT = Danish');
-----------
 Gaberd
 Gaardiner
 Gaasten

REGEXP_INSTR (source_char, pattern[, position[, occurrence[, return_opt[, match_param[, subexpr]]]]])

参数	说明
source_char	指定用作搜索值的字符表达式，数据类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。
pattern	指定正则表达式截取规则。它通常是一个文本文字，字符类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 或 `NVARCHAR2` 。
position	指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1 ，表示从第一个字符开始搜索 `source_char` 。
occurrence	指定 `source_char` 中第几个正则表达式匹配位置，为可选项。默认值为 `1` 。如果 `occurrence` 大于 `1` ，则从第一次匹配后的第一个字符开始搜索第二次正则表达式匹配，依此类推。
return_opt	指定返回出现匹配值位置选项，为可选项。默认值为 `0` 。如果指定 `0` ，则返回匹配的第一个字符的位置。如果指定 `1` ，则返回匹配的最后字符后一位的位置。
match_param	指定更改正则表达式默认匹配方式，为可选项。是数据类型 `VARCHAR2` 或 `CHAR` 的字符表达式。 `i` 表示大小写不敏感。 `c` 表示大小写敏感。 `n` 表示句点 . 可以匹配换行符。 `m` 表示多行模式。 `x` 表示忽略空格字符，默认情况下，空格字符会相互匹配。
subexpr	指示 `pattern` 要返回的子表达式，是一个从 `0` 到 `9` 的非负整数，为可选项。默认值为 `0` ，表示返回第一个子表达式出现的位置。

SELECT regexp_instr('abcdefghi', 'd.f') FROM DUAL;
 regexp_instr 
--------------
            4

INSTR(char1,char2[,i[,j]])

参数	说明
char1	被搜索的字符串。字符串类型可以为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。
char2	指定要搜索的字符串。字符串类型可以为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。
i	搜索的开始位置，默认值为 `1` 。当值小于 `0` 时，搜索从反方向开始，但是仍返回被搜索到的字符的正序位置。
j	`char2` 第 `j` 次出现的位置，为可选项。默认值为 `1` 。

SELECT INSTR('PolarDB-PG','P',1,2) "INSTR" FROM DUAL;
 INSTR 
-------
     9

TRIM([{{ LEADING | TRAILING | BOTH }[ trim_character ]|trim_character }FROM] trim_source)

参数	说明
LEADING \| TRAILING \| BOTH	指定删除字符的位置。 `LEADING` 为开头字符。如果指定 `LEADING` ，则删除所有与前导字符相等的 `trim_character` 。 `TRAILING` 为结尾字符。如果指定 `TRAILING` ，则删除所有与结尾字符相等的 `trim_character` 。 `BOTH` 为开头和结尾字符。如果您指定 `BOTH` 或三个都不指定，则删除与前导和结尾字符相等的 `trim_character` 。
trim_character	删除的字符，只能是单个字符。如果未指定 `trim_character` ，则默认值为空格。
trim_source	需要被删除字符的字符串。如果仅指定 `trim_source` ，则删除前导和尾随空格。

SELECT trim(both 'x' from 'x!@#$x') FROM DUAL;
 btrim 
-------
 !@#$

TREAT( expr AS [ REF ] [ schema. ]type)

参数	说明
expr	字符串类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 或 `NVARCHAR2` 。
REF	可选，指定的REF，只有在expr为REF类型时才可以指定
schema	可选，指定type的schema
type	指定的声明类型

SELECT name, TREAT(VALUE(p) AS employee_t).salary salary FROM persons p;
NAME                          SALARY
------------------------- ----------
Joe                           100000
Tim                             1000

SOUNDEX(char)

参数	说明
char	字符串类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 或 `NVARCHAR2` 。

create table test(c varchar(100));
insert into test values('Smith');
select c from test where SOUNDEX(c) = SOUNDEX('SMYTHE');
-------
 Smith

REGEXP_SUBSTR(source_char, pattern
               [, position [, occurrence [, match_param [, subexpr] ] ] ]
              )

参数	说明
source_char	指定用作搜索值的字符表达式，数据类型可以为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。
pattern	指定正则表达式截取规则。它通常是一个文本字面量，字符类型可以为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 或 `NVARCHAR2` 。
position	指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是1，表示从第一个字符开始搜索 `source_char` 。
occurrence	指定 `source_char` 中第几个正则表达式匹配位置，为可选项。默认值为1。如果 `occurrence` 大于1，则从第一次匹配后的第一个字符开始搜索第二次正则表达式匹配，依此类推。
match_param	指定更改正则表达式默认匹配方式，为可选项。是数据类型 `VARCHAR2` 或 `CHAR` 的字符表达式。 `i` 表示大小写不敏感。 `c` 表示大小写敏感。 `n` 表示句点 `.` 可以匹配换行符。 `m` 表示多行模式。 `x` 表示忽略空格字符，默认情况下，空格字符会相互匹配。
subexpr	指示 `pattern` 要返回的子表达式，是一个从0到9的非负整数，为可选项。默认值为0，表示返回第一个子表达式。

SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,') "REGEXPR_SUBSTR" FROM DUAL;
  REGEXPR_SUBSTR   
-------------------
 , Redwood Shores,
SELECT REGEXP_SUBSTR('http://www.oracle.com/products','http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR" FROM DUAL;
     REGEXP_SUBSTR      
------------------------
 http://www.oracle.com/

NLS_LOWER(char [, 'nlsparam' ])

参数	说明
char	指定要转换的字符串。字符串类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 、 `NVARCHAR2` 或 `CLOB` 。

select NLS_LOWER('aaa BBB CcC') FROM DUAL;
  nls_lower  
-------------
 aaa bbb ccc

NLS_INITCAP(c)

参数	说明
c	字符串类型可为 `CHAR` 、 `VARCHAR2` 、 `NCHAR` 或 `NVARCHAR2` 。

select INITCAP('aaa BBB CcC') FROM DUAL;
   initcap   
-------------
 Aaa Bbb Ccc

SQL字符串函数和操作符

其他字符串函数

format

NLS_UPPER

描述

语法

参数

返回类型

示例

NLSSORT

描述

语法

参数

返回类型

示例

REGEXP_INSTR

描述

语法

参数

返回类型

示例

INSTR

描述

语法

参数

返回类型

示例

TRIM

描述

语法

参数

返回类型

示例

TREAT

描述

语法

参数

返回类型

示例

SOUNDEX

描述

语法

参数

返回类型

示例

REGEEXP_SUBSTR

描述

语法

参数

返回类型

示例

NLS_LOWER

描述

语法

参数

返回类型

示例

NLS_INTCAP

描述

语法

参数

返回类型

示例