华为云云数据库GaussDB字符处理函数和操作符_云淘科技
GaussDB提供的字符处理函数和操作符主要用于字符串与字符串、字符串与非字符串之间的连接，以及字符串的模式匹配操作。
bit_length(string)
描述：字符串的位数。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT bit_length('world');
bit_length
------------
40
(1 row)
|
btrim(string text [, characters text])
描述：从string开头和结尾删除只包含characters中字符（缺省是空白）的最长字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT btrim('sring' , 'ing');
btrim
-------
sr
(1 row)
|
char_length(string)或character_length(string)
描述：字符串中的字符个数。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT char_length('hello');
char_length
-------------
5
(1 row)
|
dump(expr[, return_fmt [, start_position [, length ] ] ])
描述：返回输入表达式的数据类型代码、字节长度和内部表示形式。return_fmt指定内部表现形式的进制，start_position 指定从第几个字节开始，length 表示读取的长度。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# select dump('abc测试');
dump
-------------------------------------------------
Typ=705 Len=9: 97,98,99,230,181,139,232,175,149
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
instr(text,text,int,int)
描述：instr(string1,string2,int1,int2)返回在string1中从int1位置开始匹配到第int2次string2的位置，第一个int表示开始匹配起始位置，第二个int表示匹配的次数。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
instr
-------
6
(1 row)
|
instrb(text,text,int,int)
描述：instrb(string1,string2,int1,int2)返回在string1中从int1位置开始匹配到第int2次string2的位置，第一个int表示开始匹配起始位置，第二个int表示匹配的次数。与instr函数不同的是，instrb固定以字节为单位，不受所使用的字符集影响。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT instrb( 'abcdabcdabcd', 'bcd', 2, 2 );
instrb
-------
6
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下有效。
参数int1，int2入参若为小数则不会被四舍五入，而是被截断。
lengthb(text/bpchar)
描述：获取指定字符串的字节数。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT lengthb('hello');
lengthb
---------
5
(1 row)
|
left(str text, n int)
描述：返回字符串的前n个字符。当n是负数时，返回除最后|n|个字符以外的所有字符。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT left('abcde', 2);
left
------
ab
(1 row)
|
length(string bytea, encoding name )
描述：指定encoding编码格式的string的字符数。在这个编码格式中，string必须是有效的。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT length('jose', 'UTF8');
length
--------
4
(1 row)
|
如果是查询bytea类型的长度，指定utf8编码时，最大长度只能为536870888。
lpad(string text, length int [, fill text])
描述：通过填充字符fill（缺省时为空白），把string填充为length长度。如果string已经比length长则将其尾部截断。
返回值类型：text
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下：
参数length表示字符串显示长度，单个字符的显示长度按照ORA兼容处理。当lpad函数执行过程中出现length剩余长度为1且下一个字符显示长度为2时，在字符串左侧添加一个空格字符。
参数length入参若为小数则不会被四舍五入，而是被截断。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
gaussdb=# SELECT lpad('hi', 5, 'xyza');
lpad
-------
xyzhi
(1 row)
ORA兼容模式：
gaussdb=# select lpad('expr1',7, '中国');
lpad
---------
中expr1
(1 row)
gaussdb=# select lpad('expr1',8, '中国');
lpad
----------
中expr1
(1 row)
|
notlike(x bytea name text, y bytea text)
描述：比较x和y是否不一致。
返回值类型：Boolean
示例：
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# SELECT notlike(1,2);
notlike
--------------
t
(1 row)
gaussdb=# SELECT notlike(1,1);
notlike
--------------
f
(1 row)
|
octet_length(string)
描述：字符串中的字节数。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT overlay('hello' placing 'world' from 2 for 3 );
overlay
---------
hworldo
(1 row)
|
position(substring in string)
描述：指定子字符串的位置。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# SELECT position('ing' in 'string');
position
----------
4
(1 row)
|
pg_client_encoding()
描述：当前客户端编码名称。
返回值类型：name
示例：
1 2 3 4 5 |
gaussdb=# SELECT pg_client_encoding(); pg_client_encoding -------------------- UTF8 (1 row) |
quote_ident(string text)
描述：将给定的参数值转化为text，加上引号作为文本。只有在必要的时候才会添加引号（字符串包含非标识符字符或者会转换大小写的字符）。返回值中嵌入的引号都写了两次。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT quote_ident('hello world');
quote_ident
--------------
"hello world"
(1 row)
|
quote_literal(string text)
描述：将给定的参数值转化为text，加上引号作为文本。
支持显示转换成字符类型后的XML类型数据。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT quote_literal('hello');
quote_literal
---------------
'hello'
(1 row)
|
如果出现如下写法，text文本将进行转义。
1 2 3 4 5 |
gaussdb=# SELECT quote_literal(E'O\'hello'); quote_literal --------------- 'O''hello' (1 row) |
如果出现如下写法，反斜杠会写入两次。
1 2 3 4 5 |
gaussdb=# SELECT quote_literal('O\hello');
quote_literal
---------------
E'O\hello'
(1 row)
|
如果参数为NULL，返回空。如果参数可能为null，通常使用函数quote_nullable更适用。
1 2 3 4 5 |
gaussdb=# SELECT quote_literal(NULL); quote_literal --------------- (1 row) |
quote_literal(value anyelement)
描述：将给定的值强制转换为text，加上引号作为文本。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT quote_literal(42.5); quote_literal --------------- '42.5' (1 row) |
如果出现如下写法，定值将进行转义。
1 2 3 4 5 |
gaussdb=# SELECT quote_literal(E'O\'42.5'); quote_literal --------------- '0''42.5' (1 row) |
如果出现如下写法，反斜杠会写入两次。
1 2 3 4 5 |
gaussdb=# SELECT quote_literal('O\42.5');
quote_literal
---------------
E'O\42.5'
(1 row)
|
quote_nullable(string text)
描述：将给定的参数值转化为text，加上引号作为文本。
支持显示转换成字符类型后的XML类型数据。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable('hello');
quote_nullable
----------------
'hello'
(1 row)
|
如果出现如下写法，text文本将进行转义。
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable(E'O\'hello'); quote_nullable ---------------- 'O''hello' (1 row) |
如果出现如下写法，反斜杠会写入两次。
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable('O\hello');
quote_nullable
----------------
E'O\hello'
(1 row)
|
如果参数为NULL，返回NULL。
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable(NULL); quote_nullable ---------------- NULL (1 row) |
quote_nullable(value anyelement)
描述：将给定的参数值转化为text，加上引号作为文本。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable(42.5); quote_nullable ---------------- '42.5' (1 row) |
如果出现如下写法，定值将进行转义。
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable(E'O\'42.5'); quote_nullable ---------------- 'O''42.5' (1 row) |
如果出现如下写法，反斜杠会写入两次。
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable('O\42.5');
quote_nullable
----------------
E'O\42.5'
(1 row)
|
如果参数为NULL，返回NULL。
1 2 3 4 5 |
gaussdb=# SELECT quote_nullable(NULL); quote_nullable ---------------- NULL (1 row) |
similar_escape(pat text, esc text)
描述：将一个 SQL:2008风格的正则表达式转换为POSIX风格。
返回值类型：text
示例：
substring_inner(string [from int] [for int])
描述：截取子字符串，from int表示从第几个字符开始截取，for int表示截取几个字节。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# 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 |
gaussdb=# SELECT substring('Thomas' from '...$');
substring
-----------
mas
(1 row)
gaussdb=# SELECT substring('foobar' from 'o(.)b');
result
--------
o
(1 row)
gaussdb=# SELECT substring('foobar' from '(o(.)b)');
result
--------
oob
(1 row)
|
如果POSIX正则表达式模式包含任何圆括号，那么将返回匹配第一对子表达式（对应第一个左圆括号的） 的文本。如果你想在表达式里使用圆括号而又不想导致这个例外，那么你可以在整个表达式外边放上一对圆括号。
substring(string from pattern for escape)
描述：截取匹配SQL正则表达式的子字符串。声明的模式必须匹配整个数据串，否则函数失败并返回空值。为了标识在成功的时候应该返回的模式部分，模式必须包含逃逸字符的两次出现，并且后面要跟上双引号（”）。匹配这两个标记之间的模式的文本将被返回。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT substring('Thomas' from '%#"o_a#"_' for '#');
substring
-----------
oma
(1 row)
|
rawcat(raw,raw)
描述：字符串拼接函数。
返回值类型：raw
示例：
1 2 3 4 5 |
gaussdb=# SELECT rawcat('ab','cd');
rawcat
--------
ABCD
(1 row)
|
regexp_like(text,text,text)
描述：正则表达式的模式匹配函数。
返回值类型：bool
示例：
1 2 3 4 5 |
gaussdb=# 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所示。
|
选项 |
描述 |
|---|---|
|
‘b’ |
按照无扩展的 BRE 规则匹配。 |
|
‘c’ |
大小写敏感匹配。 |
|
‘e’ |
按照扩展的 ERE 规则匹配。 |
|
‘i’ |
大小写不敏感匹配。 |
|
‘m’ |
多行模式匹配。flags 中包含’m’ 时，按照多行模式匹配，否则按照单行模式匹配。 |
|
‘n’ |
n选项的含义和GUC参数behavior_compat_options及数据库当前的兼容模式有关： 数据库SQL语法兼容模式为ORA或MYSQL，且GUC参数behavior_compat_options值包含aformat_regexp_match时，n选项表示“.”能够匹配换行符(‘ |
|
‘p’ |
部分新行敏感的匹配，影响.和方括号表达式，和新行敏感的匹配(‘m’或’n’)一样，但是不影响^和$。 |
|
‘q’ |
普通字符匹配。 |
|
‘s’ |
单行模式匹配，含义与m、n相反。 |
|
‘t’ |
紧凑模式匹配，空白符匹配自身。 |
|
‘w’ |
逆部分新行匹配，与p含义相反。 |
|
‘x’ |
宽松模式匹配，忽略空白符。 |
返回值类型：text
示例：
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT regexp_substr('str','[ac]');
regexp_substr
---------------
(1 row)
gaussdb=# 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 flags 支持的选项值所示。
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下，以’\’结尾的pattern参数为合法的。
返回值类型：int
示例：
gaussdb=# 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 flags 支持的选项值所示。
返回值类型：int
示例：
gaussdb=# SELECT regexp_instr('foobarbaz','b(..)', 1, 1, 0) AS RESULT;
result
--------
4
(1 row)
gaussdb=# 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 |
gaussdb=# SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
regexp_matches
----------------
{bar,beque}
(1 row)
gaussdb=# SELECT regexp_matches('foobarbequebaz', 'barbeque');
regexp_matches
----------------
{barbeque}
(1 row)
gaussdb=# SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
result
--------------
{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 |
gaussdb=# 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 |
gaussdb=# SELECT regexp_split_to_table('hello world', E'\s+');
regexp_split_to_table
-----------------------
hello
world
(2 rows)
|
repeat(string text, number int )
描述：将string重复number次。
返回值类型：text。
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT replace('abcdefabcdef', 'cd', 'XXX');
replace
----------------
abXXXefabXXXef
(1 row)
|
replace(string, substring)
描述：删除字符串string里出现的所有子字符串substring的内容。
string类型：text
substring类型：text
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT replace('abcdefabcdef', 'cd');
replace
----------------
abefabef
(1 row)
|
reverse(str)
描述：返回颠倒的字符串（按字符颠倒）。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT right('abcde', 2);
right
-------
de
(1 row)
gaussdb=# SELECT right('abcde', -2);
right
-------
cde
(1 row)
|
rpad(string text, length int [, fill text])
描述：使用填充字符fill（缺省时为空白），把string填充到length长度。如果string已经比length长则将其从尾部截断。
返回值类型：text
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下：
参数length表示字符串显示长度，单个字符的显示长度按照ORA兼容处理。当rpad函数执行过程中出现length剩余长度为1且下一个字符显示长度为2时，在字符串右侧添加一个空格字符。
参数length入参若为小数则不会被四舍五入，而是被截断。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
gaussdb=# SELECT rpad('hi', 5, 'xy');
rpad
-------
hixyx
(1 row)
ORA兼容模式：
gaussdb=# select rpad('expr1', 7, '中国') || '*';
?column?
----------
expr1中*
(1 row)
gaussdb=# select rpad('expr1', 8, '中国') || '*';
?column?
-----------
expr1中 *
(1 row)
|
substrb(text,int,int)
描述：提取子字符串，第一个int表示提取的起始位置，第二个表示提取几位字符。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT substrb('string',2,3);
substrb
---------
tri
(1 row)
|
substrb(text,int)
描述：提取子字符串，int表示提取的起始位置。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT substrb('string',2);
substrb
---------
tring
(1 row)
|
substr(bytea,from,count)
描述：从参数bytea中抽取子字符串。from表示抽取的起始位置，count表示抽取的子字符串长度。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT substr('string',2,3);
substr
--------
tri
(1 row)
|
string || string
描述：连接字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT 'MPP'||'DB' AS RESULT; result -------- MPPDB (1 row) |
string || non-string或non-string || string
描述：连接字符串和非字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# 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 |
gaussdb=# SELECT strpos('source', 'rc');
strpos
--------
4
(1 row)
|
to_hex(number int or bigint)
描述：把number转换成十六进制表现形式。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT translate('12345', '143', 'ax');
translate
-----------
a2x5
(1 row)
|
length(string)
描述：获取参数string中字符的数目。
返回值类型：integer
示例：
1 2 3 4 5 |
gaussdb=# SELECT length('abcd');
length
--------
4
(1 row)
|
lengthb(string)
描述：获取参数string中字节的数目。与字符集有关，同样的中文字符，在GBK与UTF8中，返回的字节数不同。
返回值类型：integer
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT substr('ABCDEF',2);
substr
--------
BCDEF
(1 row)
|
from为负数时：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT substr('ABCDEF',2,2);
substr
--------
BC
(1 row)
|
from为负数时：
1 2 3 4 5 |
gaussdb=# SELECT substr('ABCDEF',-3,2);
substr
--------
DE
(1 row)
|
substrb(string,from)
描述：该函数和SUBSTR(string,from)函数功能一致，但是计算单位为字节。
返回值类型：bytea
示例：
1 2 3 4 5 |
gaussdb=# SELECT substrb('ABCDEF',-2);
substrb
---------
EF
(1 row)
|
substrb(string,from,count)
描述：该函数和SUBSTR(string,from,count)函数功能一致，但是计算单位为字节。
返回值类型：bytea
示例：
1 2 3 4 5 |
gaussdb=# SELECT substrb('ABCDEF',2,2);
substrb
---------
BC
(1 row)
|
to_single_byte(char)
描述：将字符串中所有多字节字符转换为单字节字符。
返回值类型：text
示例：
下面的示例说明了从UTF8多字节字符‘ ＡＢＣ１２３’转换为单字节‘ABC123’。
1 2 3 4 5 |
gaussdb=# select to_single_byte('ＡＢ１２３');
to_single_byte
----------------
AB123
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
to_multi_byte(char)
描述：将字符串中所有单字节字符转换为多字节字符。
返回值类型：text
示例：
下面的示例说明了从单字节‘ABC123’转换为UTF8多字节字符‘ ＡＢＣ１２３’。
1 2 3 4 5 |
gaussdb=# select to_multi_byte('ABC123');
to_multi_byte
---------------
ＡＢＣ１２３
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
trim([leading |trailing |both] [characters] from string)
描述：从字符串string的开头、结尾或两边删除只包含characters中字符（缺省是一个空白）的最长的字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT trim(BOTH 'x' FROM 'xTomxx'); btrim ------- Tom (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT trim(LEADING 'x' FROM 'xTomxx'); ltrim ------- Tomxx (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT trim(TRAILING 'x' FROM 'xTomxx'); rtrim ------- xTom (1 row) |
rtrim(string [, characters])
描述：从字符串string的结尾删除只包含characters中字符（缺省是个空白）的最长的字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT rtrim('TRIMxxxx','x');
rtrim
-------
TRIM
(1 row)
|
ltrim(string [, characters])
描述：从字符串string的开头删除只包含characters中字符（缺省是一个空白）的最长的字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT ltrim('xxxxTRIM','x');
ltrim
-------
TRIM
(1 row)
|
upper(string)
描述：把字符串转化为大写。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT upper('tom');
upper
-------
TOM
(1 row)
|
lower(string)
描述：把字符串转化为小写。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT lower('TOM');
lower
-------
tom
(1 row)
|
nls_upper(string [, nlsparam])
描述：把字符串转化为大写，可以指定排序规则来处理某些国家语言的特殊大写转换规则。nlsparam的格式为’nls_sort=sort_name’，其中sort_name替换为具体的排序规则名。当不输入nlsparam参数时，该函数完全等同于upper。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT nls_upper('große');
nls_upper
-----------
GROßE
(1 row)
|
1 2 3 4 5 |
gaussdb=# SELECT nls_upper('große', 'nls_sort = XGerman');
nls_upper
-----------
GROSSE
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下才能使用nlsparam参数。
nls_lower(string [, nlsparam])
描述：把字符串转化为小写，可以指定排序规则来处理某些国家语言的特殊小写转换规则。nlsparam的格式为’nls_sort=sort_name’，其中sort_name替换为具体的排序规则名。当不输入nlsparam参数时，该函数完全等同于lower。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT nls_lower('INDIVISIBILITY');
nls_lower
----------------
indivisibility
(1 row)
|
1 2 3 4 5 |
gaussdb=# SELECT nls_lower('INDIVISIBILITY', 'nls_sort = XTurkish');
nls_lower
----------------
ındıvısıbılıty
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下才能使用nlsparam参数。
instr(string,substring[,position,occurrence])
描述：从字符串string的position（缺省时为1）所指的位置开始查找并返回第occurrence（缺省时为1）次出现子串substring的位置的值。
当position为0时，返回0。
当position为负数时，从字符串倒数第n个字符往前逆向搜索。n为position的绝对值。
本函数以字符为计算单位，如一个汉字为一个字符。
返回值类型：integer
示例：
1 2 3 4 5 |
gaussdb=# SELECT instr('corporate floor','or', 3);
instr
-------
5
(1 row)
|
1 2 3 4 5 |
gaussdb=# SELECT instr('corporate floor','or',-3,2);
instr
-------
2
(1 row)
|
initcap(string)
描述：将字符串中的每个单词的首字母转化为大写，其他字母转化为小写。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT initcap('hi THOMAS');
initcap
-----------
Hi Thomas
(1 row)
|
此函数在开启参数a_format_version值为10c和a_format_dev_version值为s2的情况下，才支持如中文等无大小写区分的字符输入。因此建议开启参数a_format_version值为10c和a_format_dev_version值为s2。
ascii(string)
描述：参数string的第一个字符的ASCII码。
返回值类型：integer
示例：
1 2 3 4 5 |
gaussdb=# SELECT ascii('xyz');
ascii
-------
120
(1 row)
|
ascii2(string)
描述：参数string的第一个字符在数据库字符集中的十进制编码
返回值类型：integer
示例：
1 2 3 4 5 |
gaussdb=# SELECT ascii2('xyz');
ascii2
--------
120
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
asciistr(string)
描述：把字符串string中非ASCII字符转换为\XXXX形式，其中XXXX表示UTF-16代码单元。
返回值类型：varchar
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
unistr(string)
描述：将字符串中的编码序列转化成对应字符，其他字符保持不变。
返回值类型：text
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
‘\’后必须接4位16进制字符表示编码序列，或者接另一个’\’表示输入单个’\’字符。
入参是时间类型时，时间类型会隐式转换成字符串类型。
vsize(expr)
描述：返回输入表达式的字节数。
返回值类型：int
示例：
1 2 3 4 5 |
gaussdb=# select vsize('abc测试');
vsize
-------
9
(1 row)
|
此函数在参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
replace(string varchar, search_string varchar, replacement_string varchar)
描述：把字符串string中所有子字符串search_string替换成子字符串replacement_string。
返回值类型：varchar
示例：
1 2 3 4 5 |
gaussdb=# SELECT replace('jack and jue','j','bl');
replace
----------------
black and blue
(1 row)
|
concat(str1,str2)
描述：将字符串str1和str2连接并返回，若str1或str2为NULL时，返回NULL。注意，concat会调用data type的输出函数，返回值不确定，导致优化器在生成计划的时候不能提前计算结果。如果对性能有要求，建议用 || 替代。
concat函数返回值类型为变长类型，和表中数据比较时，会因为拼接结果丢失字符串长度，导致比较结果不相等。
返回值类型：varchar
示例：
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 |
gaussdb=# SELECT concat('Hello', ' World!');
concat
--------------
Hello World!
(1 row)
gaussdb=# SELECT concat('Hello', NULL);
concat
--------
(1 row)
gaussdb=# create table test_space(c char(10));
CREATE TABLE
gaussdb=# insert into test_space values('a');
INSERT 0 1
-- 填充空格后仍然是定长字符串，预期可以查找到结果
gaussdb=# select * from test_space where c = 'a ';
c
------------
a
(1 row)
-- 拼接结果为变长字符串，比对失败，找不到结果
gaussdb=# select * from test_space where c = 'a' || ' ';
c
---
(0 rows)
|
chr(integer)
描述：如果为UTF-8字符集，将输入作为unicode编码，返回一个UTF-8的字符，其他字符集给出ASCII码的字符。
返回值类型：text
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下：参数integer入参若为小数则不会被四舍五入，而是被截断。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 |
gaussdb=# SELECT chr(65); chr ----- A (1 row) -- UTF-8字符集的情况下 gaussdb=# select chr(19968); chr ----- 一 (1 row) |
chr(cvalue int|bigint)
描述：将cvalue转换为对应字节序列的字符返回。
cvalue：cvalue支持类型是可以转换成int或bigint的类型，取值范围为[0, 2^32 – 1]，对应unsigned int的范围，根据输入n的大小返回由1-4个字节组成的字符数组。其中在不同字符集中所返回的字节数组是相同的，但由于编码规则的不同会造成返回字符串的结果依赖于字符集编码。
当字符集为单字节编码的字符集时，会先将cvalue mod 256后返回一个ASCII码字符。
注意事项：
当输入的cvalue其中的某个字节为0的时候，输出会在该处截断。
当输入不符合现字符集的编码规则时会报错。
当输入为NULL或者0时返回NULL。
返回值类型：text
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
gaussdb=# SELECT chr(65); chr ----- A (1 row) gaussdb=# select chr(16705); chr ----- AA (1 row) -- 输出被截断 gaussdb=# select chr(4259905); chr ----- A (1 row) |
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下，chr函数功能为根据输入大小返回对应字符序列：当前数据库编码字符集为多字节编码字符集时返回值为1-4个字节；当前数据库编码字符集为单字节编码字符集时返回值为将输入值通过mod 256运算后得到的单个字节。否则功能为：若当前数据库编码字符集UTF-8字符集，则将输入作为unicode编码并返回一个UTF-8字符，若当前数据库编码字符集为其他字符集则返回ASCII码字符。
regexp_substr(source_char, pattern)
描述：正则表达式的抽取子串函数。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT regexp_substr('500 Hello World, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR";
REGEXPR_SUBSTR
-------------------
, Redwood Shores,
(1 row)
|
regexp_replace(string, pattern, replacement [,flags ])
描述：替换匹配POSIX正则表达式的子字符串。 如果没有匹配pattern，那么返回不加修改的string串。 如果有匹配，则返回的string串里面的匹配子串将被replacement串替换掉。
replacement串可以包含
， 其中
是1 到9， 表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入， 并且它可以包含\&表示应该插入匹配整个模式的子串。
可选的flags参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配，g表示替换每一个匹配的子字符串而不仅仅是第一个。
返回值类型：varchar
示例：
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# SELECT regexp_replace('Thomas', '.[mN]a.', 'M');
regexp_replace
----------------
ThM
(1 row)
gaussdb=# SELECT regexp_replace('foobarbaz','b(..)', E'X\1Y', 'g') AS RESULT;
result
-------------
fooXarYXazY
(1 row)
|
repexp_replace(string text, pattern text [, replacement text [, position int [, occurrence int [, flags text]]]])
描述：替换匹配POSIX正则表达式的子字符串。如果没有匹配pattern，那么返回不加修改的string串。如果有匹配，则返回的string串里面的匹配子串将被replacement串替换掉。
参数说明：
string：用于匹配的源字符串。
pattern：用于匹配的正则表达式模式串。
replacement：可选参数，用于替换匹配子串的字符串。如果不给定参数值或者为null，表示用空串替换。
position：可选参数，表示从源字符串的第几个字符开始匹配，默认值为1。
occurrence：可选参数，表示替换第occurrence个匹配的子串。默认值为1，表示替换匹配到的第一个子串。
flags：可选参数，包含零个或多个改变函数匹配行为的单字母标记。flags 支持的选项值及含义描述如表1 flags 支持的选项值所示。
此函数在参数a_format_version值为10c和a_format_dev_version值为s1的情况下，occurrence参数默认值为0，表示替换所有匹配到的子串，并且以’\’字符结尾的pattern参数为合法的。
返回值类型：text
示例：
gaussdb=# SELECT regexp_replace('foobarbaz','b(..)', E'X\1Y', 2, 2, 'n') AS RESULT;
result
------------
foobarXazY
(1 row)
concat_ws(sep text, str”any” [, str”any” [, …] ])
描述：以第一个参数为分隔符，链接第二个以后的所有参数。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# create table test(a text);
gaussdb=# insert into test(a) values ('abC 不');
gaussdb=# insert into test(a) values ('abC 啊');
gaussdb=# insert into test(a) values ('abc 啊');
gaussdb=# select * from test order by nlssort(a,'nls_sort=schinese_pinyin_m');
a
--------
abc 啊
abC 啊
abC 不
(3 rows)
gaussdb=# select * from test order by nlssort(a, 'nls_sort=generic_m_ci');
a
--------
abC 啊
abc 啊
abC 不
(3 rows)
|
convert(string bytea, src_encoding name, dest_encoding name)
描述：以dest_encoding指定的目标编码方式转化字符串bytea。src_encoding指定源编码方式，在该编码下，string必须是合法的。
返回值类型：bytea
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# show server_encoding;
server_encoding
-----------------
LATIN1
(1 row)
gaussdb=# 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)
描述：以数据库的编码方式转化字符串bytea。
src_encoding指定源编码方式，在该编码下，string必须是合法的。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT convert_from('text_in_utf8', 'UTF8');
convert_from
--------------
text_in_utf8
(1 row)
|
convert_to(string text, dest_encoding name)
描述：将字符串转化为dest_encoding的编码格式。
返回值类型：bytea
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT 'AA_BBCC' LIKE '%A@_B%' ESCAPE '@' AS RESULT; result -------- t (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT 'AA_BBCC' LIKE '%A@_B%' AS RESULT; result -------- f (1 row) |
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT regexp_like('ABC', '[A-Z]');
regexp_like
-------------
t
(1 row)
|
1 2 3 4 5 |
gaussdb=# SELECT regexp_like('ABC', '[D-Z]');
regexp_like
-------------
f
(1 row)
|
1 2 3 4 5 |
gaussdb=# SELECT regexp_like('ABC', '[A-Z]','i');
regexp_like
-------------
t
(1 row)
|
1 2 3 4 5 |
gaussdb=# SELECT regexp_like('ABC', '[A-Z]');
regexp_like
-------------
t
(1 row)
|
format(formatstr text [, str”any” [, …] ])
描述：格式化字符串。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# 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 |
gaussdb=# SELECT md5('ABC');
md5
----------------------------------
902fbdd2b1df0c4f70b4a5d23525e932
(1 row)
|
sha(string) / sha1(string)
描述：将string使用SHA1加密，并以16进制数作为返回值，sha和sha1函数功能相同。
SHA1加密算法安全性低，存在安全风险，不建议使用。
该函数仅在GaussDB兼容MY类型时（即sql_compatibility = ‘MYSQL’）有效，其他类型不支持该函数。
返回值类型：text
示例：
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# select sha('ABC');
sha
------------------------------------------
3c01bdbb26f358bab27f267924aa2c9a03fcfdb8
(1 row)
gaussdb=# select sha1('ABC');
sha1
------------------------------------------
3c01bdbb26f358bab27f267924aa2c9a03fcfdb8
(1 row)
|
sha2(string，hash_length)
描述：将string使用SHA2加密，并以16进制数作为返回值。
hash_length：对应相应的SHA2算法，可选值为 0(SHA-256)、224(SHA-224)、256(SHA-256)、384(SHA-384)、512(SHA-512)，其他值将返回NULL。
SHA224加密算法安全性低，存在安全风险，不建议使用。
SHA2函数会在日志中会记录哈希的明文，因此不建议用户用该函数加密秘钥等敏感信息。
该函数仅在GaussDB兼容MY类型时（即sql_compatibility = ‘MYSQL’）有效，其他类型不支持该函数。
返回值类型：text
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
gaussdb=# select sha2('ABC',224);
sha2
----------------------------------------------------------
107c5072b799c4771f328304cfe1ebb375eb6ea7f35a3aa753836fad
(1 row)
gaussdb=# select sha2('ABC',256);
sha2
------------------------------------------------------------------
b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78
(1 row)
gaussdb=# select sha2('ABC',0);
sha2
------------------------------------------------------------------
b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78
(1 row)
|
decode(string text, format text)
描述：将二进制数据从文本数据中解码。
返回值类型：bytea
示例：
1 2 3 4 5 |
gaussdb=# SELECT decode('MTIzAAE=', 'base64');
decode
--------------
\x3132330001
(1 row)
|
similar_escape(pat text, esc text)
描述：将一个SQL:2008风格的正则表达式转换为POSIX风格。
返回值类型：text
示例：
gaussdb=# select similar_escape('\s+ab','2');
similar_escape
----------------
^(?:\s+ab)$
(1 row)
svals(hstore)
描述：获取hstore中的value。
返回值类型：SETOF text
示例：
1 2 3 4 5 |
gaussdb=# select svals('"aa"=>"bb"');
svals
-------
bb
(1 row)
|
tconvert(key text, value text)
描述：将字符串转换为hstore格式。
返回值类型：hstore
示例：
1 2 3 4 5 |
gaussdb=# select tconvert('aa', 'bb');
tconvert
------------
"aa"=>"bb"
(1 row)
|
find_in_set(text, set)
描述：查找给定成员在集合中的位置，从1开始计数。如果没有找到，返回0。分布式暂不支持SET数据类型，此函数执行时会报错。
返回值类型：int2
encode(data bytea, format text)
描述：将二进制数据编码为文本数据。
返回值类型：text
示例：
1 2 3 4 5 |
gaussdb=# SELECT encode(E'123\000\001', 'base64'); encode ---------- MTIzAAE= (1 row) |
若字符串中存在换行符，如字符串由一个换行符和一个空格组成，在GaussDB中LENGTH和LENGTHB的值为2。
对于CHAR(n) 类型，GaussDB中n是指字符个数。因此，对于多字节编码的字符集， LENGTHB函数返回的长度可能大于n。
扩展函数和操作符
pkg_bpchar_opc
描述：扩展接口，用于新增bpchar和text或者text和bpchar策略比较操作符，为解决bpchar类型和text类型数据比较、无法命中索引问题。仅系统管理员可以安装扩展。
示例：
bpchar类型和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 26 27 28 29 30 31 32 33 |
/*
目的就是获得所有节点的详细执行计划。
*/
gaussdb=# SET max_datanode_for_plan = 64;
SET
/*
没有安装扩展时候，nchar和text比较时候，由于没有bpchar和text索引操作符，nchar会隐式转换为text,即定长字符类型转换成变长字符类型，导致执行计划发生了变化，没有命中索引。
*/
gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' ');
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
Data Node Scan (cost=0.00..0.00 rows=0 width=0)
Node/s: All datanodes
Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log
_id::text = rpad(btrim('FE306991300002 '::text), 16, ' '::text)
Datanode Name: datanode1
Seq Scan on logs_nchar (cost=0.00..1.01 rows=1 width=584)
Filter: ((log_id)::text = 'FE306991300002 '::text)
Datanode Name: datanode2
Seq Scan on logs_nchar (cost=0.00..1.03 rows=1 width=584)
Filter: ((log_id)::text = 'FE306991300002 '::text)
(12 rows)
/*
表log_nchar里log_id字段类型是nchar(16),插入数据为'FE306991300002 '，隐式转换成text类型，进行比较时，会把后面空格去掉进行比较,即'FE306991300002'='FE306991300002 '，所以不命中数据。
*/
gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' ');
log_id | log_message
--------+-------------
(0 rows)
|
bpchar类型和text类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）:
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 26 27 28 29 30 31 32 33 |
/*
系统管理员安装pkg_bpchar_opc扩展，数据库增加了bpchar和text类型比较操作符，以及索引相关内容。
*/
gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION
gaussdb=# SET max_datanode_for_plan = 64;
SET
gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' ');
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------------
Data Node Scan (cost=0.00..0.00 rows=0 width=0)
Node/s: datanode2
Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log
_id = rpad(btrim('FE306991300002 '::text), 16, ' '::text)
Datanode Name: datanode2
[Bypass]
Index Scan using idx_nchar_logid on logs_nchar (cost=0.00..8.27 rows
=1 width=584)
Index Cond: (log_id = 'FE306991300002 '::text)
(9 rows)
/*
此时，log_id隐式转换为bpchar类型时，和text类型比较时，能找到比较操作符以及索引信息，能命中索引。
*/
gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' ');
log_id | log_message
------------------+-------------
FE306991300002 | 111
(1 row)
|
text类型和bpchar类型比较时（初始状态，前向兼容）:
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 26 27 28 |
gaussdb=# SET max_datanode_for_plan = 64;
SET
gaussdb=# EXPLAIN SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar;
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------
Data Node Scan (cost=0.00..0.00 rows=0 width=0)
Node/s: datanode2
Remote SQL: SELECT log_id, log_message FROM public.logs_text WHERE log_
id = 'FE306991300002 '::bpchar::text
Datanode Name: datanode2
[Bypass]
Index Scan using idx_text_logid on logs_text (cost=0.00..8.27 rows=1
width=548)
Index Cond: (log_id = 'FE306991300002'::text)
(9 rows)
gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar;
log_id | log_message
--------+-------------
(0 rows)
gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text;
log_id | log_message
------------------+-------------
FE306991300002 | 111
(1 row)
|
text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）:
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 26 27 28 29 30 |
gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION
gaussdb=# SET max_datanode_for_plan = 64;
SET
gaussdb=# explain select * from logs_text t1 where t1.log_id ='FE306991300002 '::bpchar;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------
Data Node Scan (cost=0.00..0.00 rows=0 width=0)
Node/s: datanode2
Remote SQL: SELECT log_id, log_message FROM public.logs_text t1 WHERE l
og_id = 'FE306991300002 '::bpchar
Datanode Name: datanode2
[Bypass]
Index Scan using idx_text_logid on logs_text t1 (cost=0.00..8.27 row
s=1 width=548)
Index Cond: (log_id = 'FE306991300002 '::bpchar)
(9 rows)
gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar;
log_id | log_message
--------+-------------
(0 rows)
gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text;
log_id | log_message
------------------+-------------
FE306991300002 | 111
(1 row)
|
hash索引、text类型和bpchar类型比较时（初始状态，前向兼容）:
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 26 27 28 29 30 31 32 33 34 |
gaussdb=# SET max_datanode_for_plan = 64;
SET
gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
log_id | log_message | log_id | log_message
------------------+-------------+------------------+-------------
FE306991300003 | 222 | FE306991300003 | 222
FE306991300004 | 222 | FE306991300004 | 222
FE306991300002 | 111 | FE306991300002 | 111
(3 rows)
gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
QUERY PLAN
------------------------------------------------------------------------
--------------
Streaming (type: GATHER) (cost=17.29..39.84 rows=20 width=1150)
Node/s: All datanodes
-> Hash Join (cost=13.29..31.42 rows=20 width=1150)
Hash Cond: (((t1.log_id)::bpchar) = t2.log_id)
-> Streaming(type: REDISTRIBUTE) (cost=0.00..17.98 rows=20 wi
dth=566)
Spawn on: All datanodes
-> Seq Scan on logs_varchar2 t1 (cost=0.00..13.13 rows=
20 width=566)
-> Hash (cost=13.13..13.13 rows=21 width=584)
-> Seq Scan on logs_char t2 (cost=0.00..13.13 rows=20 w
idth=584)
(9 rows)
gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 ';
log_id | log_message | log_id | log_message
------------------+-------------+------------------+-------------
FE306991300002 | 111 | FE306991300003 | 222
FE306991300002 | 111 | FE306991300002 | 111
FE306991300002 | 111 | FE306991300004 | 222
(3 rows)
|
hash索引、text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）:
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 26 27 28 29 30 31 32 33 34 35 36 |
gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION
gaussdb=# SET max_datanode_for_plan = 64;
SET
gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
log_id | log_message | log_id | log_message
------------------+-------------+------------------+-------------
FE306991300002 | 111 | FE306991300002 | 111
FE306991300004 | 222 | FE306991300004 | 222
FE306991300003 | 222 | FE306991300003 | 222
(3 rows)
gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
QUERY PLAN
------------------------------------------------------------------------
--------------
Streaming (type: GATHER) (cost=17.29..45.25 rows=20 width=1150)
Node/s: All datanodes
-> Hash Join (cost=13.29..36.83 rows=20 width=1150)
Hash Cond: ((t1.log_id)::text = t2.log_id)
-> Streaming(type: BROADCAST) (cost=0.00..23.37 rows=40 width
=566)
Spawn on: All datanodes
-> Seq Scan on logs_varchar2 t1 (cost=0.00..13.13 rows=
20 width=566)
-> Hash (cost=13.13..13.13 rows=21 width=584)
-> Seq Scan on logs_char t2 (cost=0.00..13.13 rows=20 w
idth=584)
(9 rows)
gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 ';
log_id | log_message | log_id | log_message
------------------+-------------+------------------+-------------
FE306991300002 | 111 | FE306991300002 | 111
FE306991300002 | 111 | FE306991300003 | 222
FE306991300002 | 111 | FE306991300004 | 222
(3 rows)
|
为了解决bpchar类型（包含多个后补空格）与text类型做等值匹配操作的时候无法正常匹配数据以及索引问题。
涉及ubtree, btree, hash索引，比较符号包含： >, >=, <, <=, 。
影响面涉及字符类型之间的隐式转换，例如：变长和定长数据类型比较时，变长会优先转换为text类型，而不是最初的bpchar类型。
默认不开启pkg_bpchar_opc扩展。检验扩展有没有开启，可以查看系统表pg_extension，有该扩展数据是开启了，没有则是没有开启。关闭扩展时，保持了前向兼容，开启扩展时，保持了与ORA数据库兼容等。开启pkg_bpchar_opc扩展后，需要设置max_datanode_for_plan参数（该参数可以控制生成FQS计划时设置显示DN上执行计划的个数），根据自身需求去设置。以设置max_datanode_for_plan参数为64为例，命令如下：
set max_datanode_for_plan = 64。
示例中所用的表结构都是以log_id为索引，共有log_id，log_message两个字段，表名下划线后面接的是log_id字段类型（例如：表名为logs_text,则log_id字段类型为text）。
|
函数名称 |
描述 |
|---|---|
|
pg_catalog.bpchar_text_lt |
bpchar类型和text类型比较，左边数值是否小于右边的数值。 |
|
pg_catalog.bpchar_text_le |
bpchar类型和text类型比较，左边数值是否小于等于右边的数值。 |
|
pg_catalog.bpchar_text_eq |
bpchar类型和text类型比较，左边数值是否等于右边的数值。 |
|
pg_catalog.bpchar_text_ge |
bpchar类型和text类型比较，左边数值是否大于等于右边的数值。 |
|
pg_catalog.bpchar_text_gt |
bpchar类型和text类型比较，左边数值是否大于右边的数值。 |
|
pg_catalog.bpchar_text_ne |
bpchar类型和text类型比较，左边数值是否不等于右边的数值。 |
|
pg_catalog.bpchar_text_cmp |
bpchar类型和text类型的索引支持比较函数。 |
|
pg_catalog.text_bpchar_lt |
text类型和bpchar 类型比较，左边数值是否小于右边的数值。 |
|
pg_catalog.text_bpchar_le |
text类型和bpchar类型比较，左边数值是否小于等于右边的数值。 |
|
pg_catalog.text_bpchar_eq |
text类型和bpchar类型比较，左边数值是否等于右边的数值。 |
|
pg_catalog.text_bpchar_ge |
text类型和bpchar类型比较，左边数值是否大于等于右边的数值。 |
|
pg_catalog.text_bpchar_gt |
text类型和bpchar类型比较，左边数值是否大于右边的数值。 |
|
pg_catalog.text_bpchar_ne |
text类型和bpchar类型比较，左边数值是否不等于右边的数值。 |
|
pg_catalog.text_bpchar_cmp |
text类型和bpchar类型的索引支持比较函数。 |
|
pg_catalog.hashbpchartext |
bpchar类型和text类型的hash支持比较函数。 |
|
pg_catalog.hashtextbpchar |
text类型和bpchar类型的hash支持比较函数。 |
父主题： 函数和操作符
同意关联代理商云淘科技，购买华为云产品更优惠（QQ 78315851）
内容没看懂？ 不太想学习？想快速解决？ 有偿解决： 联系专家
