字符处理函数和操作符
GaussDB提供的字符处理函数和操作符主要用于字符串与字符串、字符串与非字符串之间的连接,以及字符串的模式匹配操作。注意:字符串处理函数除了length相关函数,其他函数和操作符不支持大于1GB clob作为参数。
- bit_length(string)
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT bit_length('world'); bit_length ------------ 40 (1 row)
- btrim(string text [, characters text])
描述:从string开头和结尾删除只包含characters中字符(缺省是空白)的最长字符串。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT btrim('sring' , 'ing'); btrim ------- sr (1 row)
- char_length(string)或character_length(string)
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT char_length('hello'); char_length ------------- 5 (1 row)
- instr(text,text,int,int)
描述:instr(string1,string2,int1,int2)返回在string1中从int1位置开始匹配到第int2次string2的位置,第一个int表示开始匹配起始位置,第二个int表示匹配的次数。
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 ); instr ------- 6 (1 row)
- lengthb(text/bpchar)
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT lengthb('hello'); lengthb --------- 5 (1 row)
- left(str text, n int)
描述:返回字符串的前n个字符。当n是负数时,返回除最后|n|个字符以外的所有字符。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT left('abcde', 2); left ------ ab (1 row)
- length(string bytea, encoding name )
描述:指定encoding编码格式的string的字符数。在这个编码格式中,string必须是有效的。
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT length('jose', 'UTF8'); length -------- 4 (1 row)
如果是查询bytea类型的长度,指定utf8编码时,最大长度只能为536870888。
- lpad(string text, length int [, fill text])
描述:通过填充字符fill(缺省时为空白),把string填充为length长度。如果string已经比length长则将其尾部截断。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT lpad('hi', 5, 'xyza'); lpad ------- xyzhi (1 row)
- notlike(x bytea name text, y bytea text)
返回值类型:Boolean
示例:
1 2 3 4 5 6 7 8 9 10
openGauss=# SELECT notlike(1,2); notlike -------------- t (1 row) openGauss=# SELECT notlike(1,1); notlike -------------- f (1 row)
- octet_length(string)
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT octet_length('jose'); octet_length -------------- 4 (1 row)
- overlay(string placing string FROM int [for int])
描述:替换子字符串。FROM int表示从第一个string的第几个字符开始替换,for int表示替换第一个string的字符数目。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT overlay('hello' placing 'world' from 2 for 3 ); overlay --------- hworldo (1 row)
- position(substring in string)
返回值类型:int,字符串不存在时返回0。
示例:
1 2 3 4 5
openGauss=# SELECT position('ing' in 'string'); position ---------- 4 (1 row)
- pg_client_encoding()
返回值类型:name
示例:
1 2 3 4 5
openGauss=# SELECT pg_client_encoding(); pg_client_encoding -------------------- UTF8 (1 row)
- quote_ident(string text)
描述:返回适用于SQL语句的标识符形式(使用适当的引号进行界定)。只有在必要的时候才会添加引号(字符串包含非标识符字符或者会转换大小写的字符)。返回值中嵌入的引号都写了两次。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT quote_ident('hello world'); quote_ident -------------- "hello world" (1 row)
- quote_literal(string text)
描述:返回适用于SQL语句的标识符形式(使用适当的引号进行界定)。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT quote_literal('hello'); quote_literal --------------- 'hello' (1 row)
如果出现如下写法,text文本将进行转义。
1 2 3 4 5
openGauss=# SELECT quote_literal(E'O\'hello'); quote_literal --------------- 'O''hello' (1 row)
如果出现如下写法,反斜杠会写入两次。
1 2 3 4 5
openGauss=# SELECT quote_literal('O\hello'); quote_literal --------------- E'O\\hello' (1 row)
如果参数为NULL,返回空。如果参数可能为null,通常使用函数quote_nullable更适用。
1 2 3 4 5
openGauss=# SELECT quote_literal(NULL); quote_literal --------------- (1 row)
- quote_literal(value anyelement)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT quote_literal(42.5); quote_literal --------------- '42.5' (1 row)
如果出现如下写法,定值将进行转义。
1 2 3 4 5
openGauss=# SELECT quote_literal(E'O\'42.5'); quote_literal --------------- '0''42.5' (1 row)
如果出现如下写法,反斜杠会写入两次。
1 2 3 4 5
openGauss=# SELECT quote_literal('O\42.5'); quote_literal --------------- E'O\\42.5' (1 row)
- quote_nullable(string text)
描述:返回适用于在SQL语句里当作字符串使用的形式(使用适当的引号进行界定)。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT quote_nullable('hello'); quote_nullable ---------------- 'hello' (1 row)
如果出现如下写法,text文本将进行转义。
1 2 3 4 5
openGauss=# SELECT quote_nullable(E'O\'hello'); quote_nullable ---------------- 'O''hello' (1 row)
如果出现如下写法,反斜杠会写入两次。
1 2 3 4 5
openGauss=# SELECT quote_nullable('O\hello'); quote_nullable ---------------- E'O\\hello' (1 row)
如果参数为NULL,返回NULL。
1 2 3 4 5
openGauss=# SELECT quote_nullable(NULL); quote_nullable ---------------- NULL (1 row)
- quote_nullable(value anyelement)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT quote_nullable(42.5); quote_nullable ---------------- '42.5' (1 row)
如果出现如下写法,定值将进行转义。
1 2 3 4 5
openGauss=# SELECT quote_nullable(E'O\'42.5'); quote_nullable ---------------- 'O''42.5' (1 row)
如果出现如下写法,反斜杠会写入两次。
1 2 3 4 5
openGauss=# SELECT quote_nullable('O\42.5'); quote_nullable ---------------- E'O\\42.5' (1 row)
如果参数为NULL,返回NULL。
1 2 3 4 5
openGauss=# SELECT quote_nullable(NULL); quote_nullable ---------------- NULL (1 row)
- substring_inner(string [from int] [for int])
描述:截取子字符串,from int表示从第几个字符开始截取,for int表示截取几个字节。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# select substring_inner('adcde', 2,3); substring_inner ----------------- dcd (1 row)
- substring(string [from int] [for int])
描述:截取子字符串,from int表示从第几个字符开始截取,for int表示截取几个字节。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT substring('Thomas' from 2 for 3); substring ----------- hom (1 row)
- substring(string from pattern)
描述:截取匹配POSIX正则表达式的子字符串。如果没有匹配它返回空值,否则返回文本中匹配模式的那部分。
返回值类型:text
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
openGauss=# SELECT substring('Thomas' from '...$'); substring ----------- mas (1 row) openGauss=# SELECT substring('foobar' from 'o(.)b'); result -------- o (1 row) openGauss=# SELECT substring('foobar' from '(o(.)b)'); result -------- oob (1 row)
如果POSIX正则表达式模式包含任何圆括号,那么将返回匹配第一对子表达式(对应第一个左圆括号的) 的文本。如果你想在表达式里使用圆括号而又不想导致这个例外,那么你可以在整个表达式外边放上一对圆括号。
- substring(string from pattern for escape)
描述:截取匹配SQL正则表达式的子字符串。声明的模式必须匹配整个数据串,否则函数失败并返回空值。为了标识在成功的时候应该返回的模式部分,模式必须包含两个逃逸字符引用的部分,并且逃逸字符后面要添加双引号(")。匹配这两个标记之间的模式的文本将被返回。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT substring('Thomas' from '%#"o_a#"_' for '#'); substring ----------- oma (1 row)
- rawcat(raw,raw)
返回值类型:raw
示例:
1 2 3 4 5
openGauss=# SELECT rawcat('ab','cd'); rawcat -------- ABCD (1 row)
- regexp_like(text,text,text)
返回值类型:bool
示例:
1 2 3 4 5
openGauss=# SELECT regexp_like('str','[ac]'); regexp_like ------------- f (1 row)
- regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]])
描述:正则表达式的抽取子串函数。与substr功能相似,正则表达式出现多个并列的括号时,也全部处理。
参数说明:
- string:用于匹配的源字符串。
- pattern:用于匹配的正则表达式模式串。
- position:可选参数,表示从源字符串的第几个字符开始匹配,默认值为1。
- occurrence:可选参数,表示抽取第几个满足匹配的子串,默认值为1。
- flags:可选参数,包含零个或多个改变函数匹配行为的单字母标记。flags 支持的选项值及含义描述如表1所示:
表1 flags 支持的选项值 选项
描述
'b'
按照无扩展的 BRE 规则匹配。
'c'
大小写敏感匹配。
'e'
按照扩展的ERE规则匹配。
'i'
大小写不敏感匹配。
'm'
多行模式匹配。flags中包含'm'时,按照多行模式匹配,否则按照单行模式匹配。
'n'
n选项的含义和GUC参数behavior_compat_options及数据库当前的兼容模式有关:- 数据库SQL语法兼容模式为A或B,且GUC参数behavior_compat_options值包含aformat_regexp_match时,'n'选项表示“.”能够匹配换行符('\n');flags未指定'n'选项时,“.”不会匹配换行符。
- 其他情况下,'n'选项和'm'选项的含义一样。
'p'
部分新行敏感的匹配,影响'.'和方括号表达式,和新行敏感的匹配('m'或'n')一样,但是不影响^和$。
'q'
普通字符匹配。
's'
单行模式匹配,含义与'm', 'n'相反。
't'
紧凑模式匹配,空白符匹配自身。
'w'
逆部分新行匹配,与'p'含义相反。
'x'
宽松模式匹配,忽略空白符。
返回值类型:text
示例:
1 2 3 4 5 6 7 8 9 10 11
openGauss=# SELECT regexp_substr('str','[ac]'); regexp_substr --------------- (1 row) openGauss=# SELECT regexp_substr('foobarbaz', 'b(..)', 3, 2) AS RESULT; result -------- baz (1 row)
- regexp_count(string text, pattern text [, position int [, flags text]])
参数说明:
- string:用于匹配的源字符串。
- pattern:用于匹配的正则表达式模式串。
- position:表示从源字符串的第几个字符开始匹配,为可选参数,默认值为1。
- flags:可选参数,包含零个或多个改变函数匹配行为的单字母标记。flags 支持的选项值及含义描述如表1所示。
返回值类型:int
示例:
openGauss=# SELECT regexp_count('foobarbaz','b(..)', 5) AS RESULT; result -------- 1 (1 row)
- regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]])
描述:获取满足匹配条件的子串位置(从1开始)。如果没有匹配的子串,则返回0。
参数说明:
- string:用于匹配的源字符串。
- pattern:用于匹配的正则表达式模式串。
- position:可选参数,表示从源字符串的第几个字符开始匹配,默认值为1。
- occurrence:可选参数,表示获取第occurrence个匹配子串的位置,默认值为1。
- return_opt:可选参数,用于控制返回匹配子串的首字符位置还是尾字符位置。取值为0时,返回匹配子串的第一个字符的位置(从1开始计算),取值为大于0的值时,返回匹配子串的尾字符的下一个字符的位置。默认值为0。
- flags:可选参数,包含零个或多个改变函数匹配行为的单字母标记。flags 支持的选项值及含义描述如表1所示。
返回值类型:int
示例:
openGauss=# SELECT regexp_instr('foobarbaz','b(..)', 1, 1, 0) AS RESULT; result -------- 4 (1 row) openGauss=# SELECT regexp_instr('foobarbaz','b(..)', 1, 2, 0) AS RESULT; result -------- 7 (1 row)
- regexp_matches(string text, pattern text [, flags text])
描述:返回string中所有匹配POSIX正则表达式的子字符串。如果pattern不匹配,该函数不返回行。如果模式不包含圆括号子表达式,则每一个被返回的行都是一个单一元素的文本数组,其中包括匹配整个模式的子串。如果模式包含圆括号子表达式,该函数返回一个文本数组,它的第n个元素是匹配模式的第n个圆括号子表达式的子串。
flags参数为可选参数,包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配,g表示替换每一个匹配的子字符串而不仅仅是第一个。
如果提供了最后一个参数,但参数值是空字符串(''),且数据库SQL兼容模式设置为ORA的情况下,会导致返回结果为空集。这是因为ORA兼容模式将''作为NULL处理,避免此类行为的方式有如下几种:
- 将数据库SQL兼容模式改为TD。
- 不提供最后一个参数,或最后一个参数不为空字符串。
返回值类型:setof text[]
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
openGauss=# SELECT regexp_matches('foobarbequebaz', '(bar)(beque)'); regexp_matches ---------------- {bar,beque} (1 row) openGauss=# SELECT regexp_matches('foobarbequebaz', 'barbeque'); regexp_matches ---------------- {barbeque} (1 row) openGauss=# SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g'); regexp_matches -------------- {bar,beque} {bazil,barf} (2 rows)
- regexp_split_to_array(string text, pattern text [, flags text ])
描述:用POSIX正则表达式作为分隔符,分隔string。和regexp_split_to_table相同,不过regexp_split_to_array会把它的结果以一个text数组的形式返回。
返回值类型:text[]
示例:
1 2 3 4 5
openGauss=# SELECT regexp_split_to_array('hello world', E'\\s+'); regexp_split_to_array ----------------------- {hello,world} (1 row)
- regexp_split_to_table(string text, pattern text [, flags text])
描述:用POSIX正则表达式作为分隔符,分隔string。如果没有与pattern的匹配,该函数返回string。如果至少有一个匹配,对每一个匹配,它都返回从上一个匹配的末尾(或者串的开头)到这次匹配开头之间的文本。当没有更多匹配时,它返回从上一次匹配的末尾到串末尾之间的文本。
flags参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配,不设参数默认表示替换每一个匹配的子字符串,而不仅仅是替换第一个子字符串。
返回值类型:setof text
示例:
1 2 3 4 5 6
openGauss=# SELECT regexp_split_to_table('hello world', E'\\s+'); regexp_split_to_table ----------------------- hello world (2 rows)
- repeat(string text, number int )
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT repeat('Pg', 4); repeat ---------- PgPgPgPg (1 row)
由于数据库内存分配机制限制单次内存分配不可超过1GB,因此number最大值不应超过(1G-x)/lengthb(string) - 1。x为头信息长度,通常大于4字节,其具体值在不同的场景下存在差异。
- replace(string text, from text, to text)
描述:把字符串string里出现的所有子字符串from的内容替换成子字符串to的内容。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT replace('abcdefabcdef', 'cd', 'XXX'); replace ---------------- abXXXefabXXXef (1 row)
- replace(string, substring)
描述:删除字符串string里出现的所有子字符串substring的内容。
string类型:text
substring类型:text
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT replace('abcdefabcdef', 'cd'); replace ---------------- abefabef (1 row)
- reverse(str)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT reverse('abcde'); reverse --------- edcba (1 row)
- right(str text, n int)
描述:返回字符串中的后n个字符。当n是负值时,返回除前|n|个字符以外的所有字符。
返回值类型:text
示例:
1 2 3 4 5 6 7 8 9 10 11
openGauss=# SELECT right('abcde', 2); right ------- de (1 row) openGauss=# SELECT right('abcde', -2); right ------- cde (1 row)
- rpad(string text, length int [, fill text])
描述:使用填充字符fill(缺省时为空白),把string填充到length长度。如果string已经比length长则将其从尾部截断。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT rpad('hi', 5, 'xy'); rpad ------- hixyx (1 row)
- rtrim(string text [, characters text])
描述:从字符串string的结尾删除只包含characters中字符(缺省是个空白)的最长的字符串。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT rtrim('trimxxxx', 'x'); rtrim ------- trim (1 row)
- substrb(text,int,int)
描述:提取子字符串,第一个int表示提取的起始位置,第二个表示提取几位字符。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT substrb('string',2,3); substrb --------- tri (1 row)
- substrb(text,int)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT substrb('string',2); substrb --------- tring (1 row)
- substr(bytea,from,count)
描述:从参数bytea中抽取子字符串。from表示抽取的起始位置,count表示抽取的子字符串长度。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT substr('string',2,3); substr -------- tri (1 row)
- string || string
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT 'MPP'||'DB' AS RESULT; result -------- MPPDB (1 row)
- string || non-string或non-string || string
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT 'Value: '||42 AS RESULT; result ----------- Value: 42 (1 row)
- split_part(string text, delimiter text, field int)
描述:根据delimiter分隔string返回生成的第field个子字符串(从出现第一个delimiter的text为基础)。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT split_part('abc~@~def~@~ghi', '~@~', 2); split_part ------------ def (1 row)
- strpos(string, substring)
描述:指定的子字符串的位置。和position(substring in string)一样,不过参数顺序相反。
返回值类型:int
示例:
1 2 3 4 5
openGauss=# SELECT strpos('source', 'rc'); strpos -------- 4 (1 row)
- to_hex(number int or bigint)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT to_hex(2147483647); to_hex ---------- 7fffffff (1 row)
- translate(string text, from text, to text)
描述:把在string中包含的任何匹配from中的字符转换为对应的在to中的字符。如果from比to长,删掉在from中出现的额外的字符。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT translate('12345', '143', 'ax'); translate ----------- a2x5 (1 row)
- length(string)
返回值类型:integer
示例:
1 2 3 4 5
openGauss=# SELECT length('abcd'); length -------- 4 (1 row)
- lengthb(string)
描述:获取参数string中字节的数目。与字符集有关,同样的中文字符,在GBK与UTF8中,返回的字节数不同。
返回值类型:integer
示例:
1 2 3 4 5
openGauss=# SELECT lengthb('Chinese'); lengthb --------- 7 (1 row)
- substr(string,from)
从参数string中抽取子字符串。
from表示抽取的起始位置。
- from为0时,按1处理。
- from为正数时,抽取从from到末尾的所有字符。
- from为负数时,抽取字符串的后n个字符,n为from的绝对值。
返回值类型:varchar
示例:
from为正数时:
1 2 3 4 5
openGauss=# SELECT substr('ABCDEF',2); substr -------- BCDEF (1 row)
from为负数时:
1 2 3 4 5
openGauss=# SELECT substr('ABCDEF',-2); substr -------- EF (1 row)
- substr(string,from,count)
从参数string中抽取子字符串。
from表示抽取的起始位置。
count表示抽取的子字符串长度。
- from为0时,按1处理。
- from为正数时,抽取从from开始的count个字符。
- from为负数时,抽取从倒数第n个开始的count个字符,n为from的绝对值。
- count小于1时,返回null。
返回值类型:varchar
示例:
from为正数时:
1 2 3 4 5
openGauss=# SELECT substr('ABCDEF',2,2); substr -------- BC (1 row)
from为负数时:
1 2 3 4 5
openGauss=# SELECT substr('ABCDEF',-3,2); substr -------- DE (1 row)
- substrb(string,from)
描述:该函数和SUBSTR(string,from)函数功能一致,但是计算单位为字节。
返回值类型:bytea
示例:
1 2 3 4 5
openGauss=# SELECT substrb('ABCDEF',-2); substrb --------- EF (1 row)
- substrb(string,from,count)
描述:该函数和SUBSTR(string,from,count)函数功能一致,但是计算单位为字节。
返回值类型:bytea
示例:
1 2 3 4 5
openGauss=# SELECT substrb('ABCDEF',2,2); substrb --------- BC (1 row)
- trim([leading |trailing |both] [characters] from string)
描述:从字符串string的开头、结尾或两边删除只包含characters中字符(缺省是一个空白)的最长的字符串。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT trim(BOTH 'x' FROM 'xTomxx'); btrim ------- Tom (1 row)
1 2 3 4 5
openGauss=# SELECT trim(LEADING 'x' FROM 'xTomxx'); ltrim ------- Tomxx (1 row)
1 2 3 4 5
openGauss=# SELECT trim(TRAILING 'x' FROM 'xTomxx'); rtrim ------- xTom (1 row)
- rtrim(string [, characters])
描述:从字符串string的结尾删除只包含characters中字符(缺省是个空白)的最长的字符串。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT rtrim('TRIMxxxx','x'); rtrim ------- TRIM (1 row)
- ltrim(string [, characters])
描述:从字符串string的开头删除只包含characters中字符(缺省是一个空白)的最长的字符串。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT ltrim('xxxxTRIM','x'); ltrim ------- TRIM (1 row)
- upper(string)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT upper('tom'); upper ------- TOM (1 row)
- lower(string)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT lower('TOM'); lower ------- tom (1 row)
- rpad(string varchar, length int [, fill varchar])
描述:使用填充字符fill(缺省时为空白),把string填充到length长度。如果string已经比length长则将其从尾部截断。
length参数在GaussDB中表示字符长度。一个汉字长度计算为一个字符。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT rpad('hi',5,'xyza'); rpad ------- hixyx (1 row)
1 2 3 4 5
openGauss=# SELECT rpad('hi',5,'abcdefg'); rpad ------- hiabc (1 row)
- instr(string,substring[,position,occurrence])
描述:从字符串string的position(缺省时为1)所指的位置开始查找并返回第occurrence(缺省时为1)次出现子串substring的位置的值。
- 当position为0时,返回0。
- 当position为负数时,从字符串倒数第n个字符往前逆向搜索。n为position的绝对值。
本函数以字符为计算单位,如一个汉字为一个字符。
返回值类型:integer
示例:
1 2 3 4 5
openGauss=# SELECT instr('corporate floor','or', 3); instr ------- 5 (1 row)
1 2 3 4 5
openGauss=# SELECT instr('corporate floor','or',-3,2); instr ------- 2 (1 row)
- initcap(string)
描述:将字符串中的每个单词的首字母转换为大写,其他字母转换为小写。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT initcap('hi THOMAS'); initcap ----------- Hi Thomas (1 row)
- ascii(string)
返回值类型:integer
示例:
1 2 3 4 5
openGauss=# SELECT ascii('xyz'); ascii ------- 120 (1 row)
- replace(string varchar, search_string varchar, replacement_string varchar)
描述:把字符串string中所有子字符串search_string替换成子字符串replacement_string。
返回值类型:varchar
示例:
1 2 3 4 5
openGauss=# SELECT replace('jack and jue','j','bl'); replace ---------------- black and blue (1 row)
- lpad(string varchar, length int[, repeat_string varchar])
描述:在string的左侧添上一系列的repeat_string(缺省为空白)来组成一个总长度为n的新字符串。
如果string本身的长度比指定的长度length长,则本函数将把string截断并把前面长度为length的字符串内容返回。
返回值类型:varchar
示例:
1 2 3 4 5
openGauss=# SELECT lpad('PAGE 1',15,'*.'); lpad ----------------- *.*.*.*.*PAGE 1 (1 row)
1 2 3 4 5
openGauss=# SELECT lpad('hello world',5,'abcd'); lpad ------- hello (1 row)
- concat(str1,str2)
描述:将字符串str1和str2连接并返回,若str1或str2为NULL时,返回NULL。注意,concat会调用data type的输出函数,返回值不确定,导致优化器在生成计划的时候不能提前计算结果。如果对性能有要求,建议用 || 替代。
- 在sql_compatibility = 'MYSQL'的情况下,参数str1或str2为NULL会导致返回结果为NULL。
- concat函数返回值类型为变长类型,和表中数据比较时,会因为拼接结果丢失字符串长度,导致比较结果不相等。
返回值类型:varchar
示例:
1 2 3 4 5 6 7 8 9 10
openGauss=# SELECT concat('Hello', ' World!'); concat -------------- Hello World! (1 row) openGauss=# SELECT concat('Hello', NULL); concat -------- (1 row)
- chr(integer)
返回值类型:varchar
示例:
1 2 3 4 5
openGauss=# SELECT chr(65); chr ----- A (1 row)
- regexp_replace(string, pattern, replacement [,flags ])
描述:替换匹配POSIX正则表达式的子字符串。 如果没有匹配pattern,那么返回不加修改的string串。 如果有匹配,则返回的string串里面的匹配子串将被replacement串替换掉。
replacement串可以包含\n, 其中\n是1 到9, 表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入, 并且它可以包含\&表示应该插入匹配整个模式的子串。
可选的flags参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配,g表示替换每一个匹配的子字符串而不仅仅是第一个。
返回值类型:varchar
示例:
1 2 3 4 5 6 7 8 9 10
openGauss=# SELECT regexp_replace('Thomas', '.[mN]a.', 'M'); regexp_replace ---------------- ThM (1 row) openGauss=# SELECT regexp_replace('foobarbaz','b(..)', E'X\\1Y', 'g') AS RESULT; result ------------- fooXarYXazY (1 row)
- concat_ws(sep text, str"any" [, str"any" [, ...] ])
描述:以第一个参数为分隔符,连接第二个以后的所有参数。NULL参数被忽略。
- 如果第一个参数值是NULL,会导致返回结果为NULL。
- 如果第一个参数值是空字符串(''),且数据库SQL兼容模式设置为ORA的情况下,会导致返回结果为NULL。这是因为A兼容模式将''作为NULL处理,避免此类行为,可以将数据库SQL兼容模式改为MYSQL、TD或者Postgres。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT concat_ws(',', 'ABCDE', 2, NULL, 22); concat_ws ------------ ABCDE,2,22 (1 row)
- nlssort(string text, sort_method text)
描述:以sort_method指定的排序方式返回字符串在该排序模式下的编码值,该编码值可用于排序,其决定了string在这种排序模式下的先后位置。目前支持的sort_method为'nls_sort=schinese_pinyin_m'和'nls_sort=generic_m_ci'。其中,'nls_sort=generic_m_ci'仅支持纯英文不区分大小写排序。
string类型:text
sort_method类型:text
返回值类型:text
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
openGauss=# create table test(a text); openGauss=# insert into test(a) values ('abC 不'); openGauss=# insert into test(a) values ('abC 啊'); openGauss=# insert into test(a) values ('abc 啊'); openGauss=# select * from test order by nlssort(a,'nls_sort=schinese_pinyin_m'); a -------- abc 啊 abC 啊 abC 不 (3 rows) openGauss=# select * from test order by nlssort(a, 'nls_sort=generic_m_ci'); a -------- abC 啊 abc 啊 abC 不 (3 rows) openGauss=# DROP TABLE test;
- convert(string bytea, src_encoding name, dest_encoding name)
描述:以dest_encoding指定的目标编码方式转换字符串string。src_encoding指定源编码方式,在该编码下,string必须是合法的。
返回值类型:bytea
示例:
1 2 3 4 5
openGauss=# SELECT convert('text_in_utf8', 'UTF8', 'GBK'); convert ---------------------------- \x746578745f696e5f75746638 (1 row)
如果源编码格式到目标编码格式的转换规则不存在,则字符串不进行任何转换直接返回,如GBK和LATIN1之间的转换规则是不存在的,具体转换规则可以通过查看系统表pg_conversion获得。
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
openGauss=# show server_encoding; server_encoding ----------------- LATIN1 (1 row) openGauss=# SELECT convert_from('some text', 'GBK'); convert_from -------------- some text (1 row) db_latin1=# SELECT convert_to('some text', 'GBK'); convert_to ---------------------- \x736f6d652074657874 (1 row) db_latin1=# SELECT convert('some text', 'GBK', 'LATIN1'); convert ---------------------- \x736f6d652074657874 (1 row)
- convert_from(string bytea, src_encoding name)
src_encoding指定源编码方式,在该编码下,string必须是合法的。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT convert_from('text_in_utf8', 'UTF8'); convert_from -------------- text_in_utf8 (1 row)
- convert_to(string text, dest_encoding name)
返回值类型:bytea
示例:
1 2 3 4 5
openGauss=# SELECT convert_to('some text', 'UTF8'); convert_to ---------------------- \x736f6d652074657874 (1 row)
- string [NOT] LIKE pattern [ESCAPE escape-character]
如果pattern不包含百分号或者下划线,该模式只代表它本身,这时候LIKE的行为就像等号操作符。在pattern里的下划线(_)匹配任何单个字符;而一个百分号(%)匹配零或多个任何字符。
要匹配下划线或者百分号本身,在pattern里相应的字符必须前导逃逸字符。缺省的逃逸字符是反斜杠,但是用户可以用ESCAPE子句指定一个。要匹配逃逸字符本身,写两个逃逸字符。
返回值类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT 'AA_BBCC' LIKE '%A@_B%' ESCAPE '@' AS RESULT; result -------- t (1 row)
1 2 3 4 5
openGauss=# SELECT 'AA_BBCC' LIKE '%A@_B%' AS RESULT; result -------- f (1 row)
1 2 3 4 5
openGauss=# SELECT 'AA@_BBCC' LIKE '%A@_B%' AS RESULT; result -------- t (1 row)
- REGEXP_LIKE(source_string, pattern [, match_parameter])
source_string为源字符串,pattern为正则表达式匹配模式。 match_parameter为匹配选项,可取值为:
- 'i':大小写不敏感。
- 'c':大小写敏感。
- 'n':允许正则表达式元字符“.”匹配换行符。
- 'm':将source_string视为多行。
若忽略match_parameter选项,默认为大小写敏感,“.”不匹配换行符,source_string视为单行。
返回值类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT regexp_like('ABC', '[A-Z]'); regexp_like ------------- t (1 row)
1 2 3 4 5
openGauss=# SELECT regexp_like('ABC', '[D-Z]'); regexp_like ------------- f (1 row)
1 2 3 4 5
openGauss=# SELECT regexp_like('ABC', '[A-Z]','i'); regexp_like ------------- t (1 row)
1 2 3 4 5
openGauss=# SELECT regexp_like('ABC', '[A-Z]'); regexp_like ------------- t (1 row)
- format(formatstr text [, str"any" [, ...] ])
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT format('Hello %s, %1$s', 'World'); format -------------------- Hello World, World (1 row)
- md5(string)
描述:将string使用MD5加密,并以16进制数作为返回值。
MD5加密算法安全性低,存在安全风险,建议使用更安全的加密算法。
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT md5('ABC'); md5 ---------------------------------- 902fbdd2b1df0c4f70b4a5d23525e932 (1 row)
- decode(string text, format text)
返回值类型:bytea
示例:
1 2 3 4 5
openGauss=# SELECT decode('MTIzAAE=', 'base64'); decode -------------- \x3132330001 (1 row)
- similar_escape(pat text, esc text)
描述:将一个SQL:2008风格的正则表达式转换为POSIX风格。
返回值类型:text
示例:
openGauss=# select similar_escape('\s+ab','2'); similar_escape ---------------- ^(?:\\s+ab)$ (1 row)
- encode(data bytea, format text)
返回值类型:text
示例:
1 2 3 4 5
openGauss=# SELECT encode(E'123\\000\\001', 'base64'); encode ---------- MTIzAAE= (1 row)
- 若字符串中存在换行符,如字符串由一个换行符和一个空格组成,在GaussDB中LENGTH和LENGTHB的值为2。
- 对于CHAR(n) 类型,GaussDB中n是指字符个数。因此,对于多字节编码的字符集, LENGTHB函数返回的长度可能大于n。
- GaussDB支持多种类型的数据库,目前有4种,分别是ORA类型,MYSQL类型,TD类型以及Postgres类型。在不指定数据库类型时,数据库默认是ORA类型,ORA的词法分析器与另外三种不一样,在ORA中空字符串会被当作是NULL。所以,当使用ORA类型的数据库时,假如上述字符操作函数中有空字符串作为参数,会出现没有输出的情况。例如:
openGauss=# SELECT translate('12345','123',''); translate ----------- (1 row)
这是因为内核在调用相应的函数进行处理前,会判断所输入的参数中是否含有NULL,假如有,则不会调用相应的函数,因此会没有输出。而在Postgres模式下,字符串的处理方式与Postgres保持一致,因此不会有上述问题产生。