字符处理函数和操作符（下）
-
lengthb(string)
描述：获取参数string中字节的数目。与字符集有关，同样的中文字符，在GBK与UTF8中，返回的字节数不同。
返回值类型：integer。
示例：
| ``` gaussdb=# SELECT lengthb('Chinese'); lengthb --------- 7 (1 row)
| ---------------------------------------------------------------------------------- | -
substr(string,from)
从参数string中抽取子字符串。
from表示抽取的起始位置。
- from为0时，按1处理。
- from为正数时，抽取从from到末尾的所有字符。
- from为负数时，抽取字符串的后n个字符，n为from的绝对值。
返回值类型：varchar。
示例：
from为正数时：
| ``` gaussdb=# SELECT substr('ABCDEF',2); substr -------- BCDEF (1 row)
| ----------------------------------------------------------------------------- | from为负数时： | ``` 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为正数时：
| ``` gaussdb=# SELECT substr('ABCDEF',2,2); substr -------- BC (1 row)
| ----------------------------------------------------------------------------- | from为负数时： | ``` gaussdb=# SELECT substr('ABCDEF',-3,2); substr -------- DE (1 row) ``` | | ------------------------------------------------------------------------------ | -
substrb(string,from)
描述：该函数和SUBSTR(string,from)函数功能一致，但是计算单位为字节。
返回值类型：bytea。
示例：
| ``` gaussdb=# SELECT substrb('ABCDEF',-2); substrb --------- EF (1 row)
| ------------------------------------------------------------------------------- | -
substrb(string,from,count)
描述：该函数和SUBSTR(string,from,count)函数功能一致，但是计算单位为字节。
返回值类型：bytea。
示例：
| ``` gaussdb=# SELECT substrb('ABCDEF',2,2); substrb --------- BC (1 row)
| -------------------------------------------------------------------------------- | -
to_single_byte(char)
返回值类型：text。
示例：
下面的示例说明了从UTF8多字节字符‘ＡＢ１２３’转换为单字节‘AB123’。
| ``` gaussdb=# SELECT to_single_byte('ＡＢ１２３'); to_single_byte ---------------- AB123 (1 row)
| --------------------------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。 -
to_multi_byte(char)
返回值类型：text。
示例：
下面的示例说明了从单字节‘ABC123’转换为UTF8多字节字符‘ ＡＢＣ１２３’。
| ``` gaussdb=# SELECT to_multi_byte('ABC123'); to_multi_byte --------------- ＡＢＣ１２３ (1 row)
| -------------------------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。 -
trim([leading |trailing |both] [characters] from string)
描述：从字符串string的开头、结尾或两边删除只包含characters中字符（缺省是一个空白）的最长的字符串。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT trim(BOTH 'x' FROM 'xTomxx'); btrim ------- Tom (1 row)
| ----------------------------------------------------------------------------------- | | ``` gaussdb=# SELECT trim(LEADING 'x' FROM 'xTomxx'); ltrim ------- Tomxx (1 row) ``` | | ---------------------------------------------------------------------------------------- | | ``` gaussdb=# SELECT trim(TRAILING 'x' FROM 'xTomxx'); rtrim ------- xTom (1 row) ``` | | ---------------------------------------------------------------------------------------- | -
rtrim(string [, characters])
描述：从字符串string的结尾删除只包含characters中字符（缺省是个空白）的最长的字符串。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT rtrim('TRIMxxxx','x'); rtrim ------- TRIM (1 row)
| ----------------------------------------------------------------------------- | -
ltrim(string [, characters])
描述：从字符串string的开头删除只包含characters中字符（缺省是一个空白）的最长的字符串。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT ltrim('xxxxTRIM','x'); ltrim ------- TRIM (1 row)
| ----------------------------------------------------------------------------- | -
upper(string)
返回值类型：text。
示例：
| ``` gaussdb=# SELECT upper('tom'); upper ------- TOM (1 row)
| ------------------------------------------------------------------- | -
lower(string)
返回值类型：text。
示例：
| ``` gaussdb=# SELECT lower('TOM'); lower ------- tom (1 row)
| ------------------------------------------------------------------- | -
nls_upper(string [, nlsparam])
描述：把字符串转换为大写，可以指定排序规则来处理某些国家语言的特殊大写转换规则。nlsparam的格式为'nls_sort=sort_name'，其中sort_name替换为具体的排序规则名。当不输入nlsparam参数时，该函数完全等同于upper。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT nls_upper('große'); nls_upper ----------- GROßE (1 row)
| ------------------------------------------------------------------------------------ | | ``` gaussdb=# SELECT nls_upper('große', 'nls_sort = XGerman'); nls_upper ----------- GROSSE (1 row) ``` | | ----------------------------------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下才支持使用。 -
nls_lower(string [, nlsparam])
描述：把字符串转换为小写，可以指定排序规则来处理某些国家语言的特殊小写转换规则。nlsparam的格式为'nls_sort=sort_name'，其中sort_name替换为具体的排序规则名。当不输入nlsparam参数时，该函数完全等同于lower。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT nls_lower('INDIVISIBILITY'); nls_lower ---------------- indivisibility (1 row)
| ---------------------------------------------------------------------------------------------------------------- | | ``` gaussdb=# SELECT nls_lower('INDIVISIBILITY', 'nls_sort = XTurkish'); nls_lower ---------------- ındıvısıbılıty (1 row) ``` | | --------------------------------------------------------------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下才支持使用。 -
instr(string,substring[,position,occurrence])
描述：从字符串string的position（缺省时为1）所指的位置开始查找并返回第occurrence（缺省时为1）次出现子串substring的位置的值。
- 当position为0时，返回0。
- 当position为负数时，从字符串倒数第n个字符往前逆向搜索。n为position的绝对值。
本函数以字符为计算单位，如一个汉字为一个字符。
返回值类型：integer。
示例：
| ``` gaussdb=# SELECT instr('corporate floor','or', 3); instr ------- 5 (1 row)
| ------------------------------------------------------------------------------------------ | | ``` gaussdb=# SELECT instr('corporate floor','or',-3,2); instr ------- 2 (1 row) ``` | | -------------------------------------------------------------------------------------------- | -
initcap(string)
描述：将字符串中的每个单词的首字母转换为大写，其他字母转换为小写。
返回值类型：text。
示例：
| ``` 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)
返回值类型：integer。
示例：
| ``` gaussdb=# SELECT ascii('xyz'); ascii ------- 120 (1 row)
| ---------------------------------------------------------------------- | -
ascii2(string)
描述：参数string的第一个字符在数据库字符集中的十进制编码。
返回值类型：integer。
示例：
| ``` gaussdb=# SELECT ascii2('xyz'); ascii2 -------- 120 (1 row) -- UTF-8字符集的情况下 gaussdb=# select ascii2('中xyz'); ascii2 ---------- 14989485 (1 row)
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。 -
asciistr(string)
描述：把字符串string中非ASCII字符转换为\XXXX形式，其中XXXX表示UTF-16代码单元。
返回值类型：varchar。
示例：
| ``` gaussdb=# SELECT asciistr('xyz中'); asciistr ---------- xyz\4E2D (1 row)
| ---------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。 -
unistr(string)
描述：将字符串中的编码序列转换成对应字符，其他字符保持不变。
返回值类型：text。
示例：
| ``` gaussdb=# select unistr( 'abc\0041\4E2D' ); unistr -------- abcA中 (1 row)
| ------------------------------------------------------------------------------------- |  - 此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。 - '\'后必须接4位16进制字符表示编码序列，或者接另一个'\'表示输入单个'\'字符。 - 入参是时间类型时，时间类型会隐式转换成字符串类型。 -
vsize(expr)
返回值类型：int。
示例：
| ``` gaussdb=# select vsize('abc测试'); vsize ------- 9 (1 row)
| ------------------------------------------------------------------------ |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。 -
replace(string varchar, search_string varchar, replacement_string varchar)
描述：把字符串string中所有子字符串search_string替换成子字符串replacement_string。
返回值类型：varchar。
示例：
| ``` gaussdb=# SELECT replace('jack and jue','j','bl'); replace ---------------- black and blue (1 row)
| --------------------------------------------------------------------------------------------------------------------- | -
concat(str1,str2)
描述：将字符串str1和str2连接并返回。注意，concat会调用data type的输出函数，返回值不确定，导致优化器在生成计划的时候不能提前计算结果。如果对性能有要求，建议用 || 替代。
- 在sql_compatibility = 'MYSQL'的情况下，参数str1或str2为NULL会导致返回结果为NULL。
- concat函数返回值类型为变长类型，和表中数据比较时，会因为拼接结果丢失字符串长度，导致比较结果不相等。
返回值类型：varchar。
示例：
| ``` 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。
- 此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s1的情况下：参数integer入参若为小数则不会被四舍五入，而是被截断。
- 如需设置、修改字符集与字符序，请参考字符集与字符序章节。
示例：
| ``` gaussdb=# SELECT chr(65); chr ----- A (1 row) -- UTF-8字符集的情况下 gaussdb=# select chr(19968); chr ----- 一 (1 row)
| ----------------------------------------------------------------------------------------------------------------------------- | -
chr(cvalue int|bigint)
cvalue：cvalue支持类型是可以转换成int或bigint的类型，取值范围为[0, 2^32 - 1]，对应unsigned int的范围，根据输入n的大小返回由1-4个字节组成的字符数组。其中在不同字符集中所返回的字节数组是相同的，但由于编码规则的不同会造成返回字符串的结果依赖于字符集编码。
当字符集为单字节编码的字符集时，会先将cvalue mod 256后返回一个ASCII码字符。
注意事项：
- 当输入的cvalue其中的某个字节为0的时候，输出会在该处截断。
- 当输入不符合现字符集的编码规则时会报错。
- 当输入为NULL或者0时返回NULL。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT chr(65); chr ----- A (1 row) gaussdb=# CREATE DATABASE gaussdb_o WITH DBCOMPATIBILITY 'ORA'; gaussdb=# \c gaussdb_o gaussdb_o=# SET a_format_version='10c'; gaussdb_o=# SET a_format_dev_version = 's1'; gaussdb_o=# SELECT chr(16705); chr ----- AA (1 row) -- 输出被截断 gaussdb_o=# SELECT chr(4259905); chr ----- A (1 row) gaussdb_o=# \c postgres gaussdb=# DROP DATABASE gaussdb_o;
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |  此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s1的情况下，chr函数功能为根据输入大小返回对应字符序列：当前数据库编码字符集为多字节编码字符集时返回值为1-4个字节；当前数据库编码字符集为单字节编码字符集时返回值为将输入值通过mod 256运算后得到的单个字节。否则功能为：若当前数据库编码字符集为UTF-8字符集，则将输入作为unicode编码并返回一个UTF-8字符，若当前数据库编码字符集为其他字符集则返回ASCII码字符。 -
nchr(cvalue int|bigint)
描述：nchr(cvalue int|bigint)返回入参在国家字符集中对应的字符。国家字符集由GUC参数nls_nchar_characterset控制，仅支持AL16UTF16和UTF8。该函数在ORA兼容模式数据库中且GUC参数a_format_version值为10c和a_format_dev_version值为s4的情况下有效。如果nls_nchar_characterset为AL16UTF16，入参超过两字节，会进行截断，保留低两位字节。如果nls_nchar_characterset为UTF8，入参超过三字节，会被当作0处理。
参数：cvalue。cvalue支持可以转换成int或bigint的类型，取值范围为[0, 2^32 - 1]，对应unsigned int的范围。入参若有小数部分，小数部分会被去掉。
返回值类型：NVARCHAR2。
- 函数的返回值字节长度与ORA数据库不一致。
- 函数返回值受限于数据库字符集，在不同的数据库字符集下，如果没有对应的UTF8转数据库字符集的映射表，或者映射表中不存在该UTF8编码，表明当前数据库字符集不支持入参对应的UTF8字符，会导致nchr(cvalue int|bigint)函数返回结果与ORA数据库不一致。
- 当前数据库字符集不支持入参对应的UTF8字符，或者国家字符集为UTF8时但是入参不符合UTF8格式，这两种情况会返回入参对应的字节数组。单个字节在[0x00-0x7F]范围，会返回一个ASCII码字符，在[0x80-0xFF]范围，会返回“?”。
- 如需设置、修改字符集与字符序，请参考字符集与字符序章节。
示例：
| ``` --数据库字符集为UTF8，nls_nchar_characterset为AL16UTF16情况下 gaussdb=# SELECT nchr(65); nchr ----- A (1 row) gaussdb=# SELECT nchr(14989440); nchr ------ 뢀 (1 row) --数据库字符集为UTF8，nls_nchar_characterset为UTF8情况下 gaussdb=# SELECT nchr(14989440); nchr ------ 一 (1 row) gaussdb=# SELECT nchr(4321090); nchr ------ A?B (1 row) --数据库字符集为gb18030，nls_nchar_characterset为AL16UTF16情况下 gaussdb=# SELECT nchr(14989440); nchr ------ 뢀 (1 row) --数据库字符集为gb18030，nls_nchar_characterset为UTF8情况下 gaussdb=# SELECT nchr(14989440); nchr ------ 一 (1 row)
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -
regexp_substr(source_char, pattern)
描述：正则表达式的抽取子串函数。SQL语法兼容ORA和MYSQL的情况下，GUC参数behavior_compat_options的值包含aformat_regexp_match时，“. ”不能匹配 '\n' 字符；不包含aformat_regexp_match时，“. ”能够匹配'\n'字符。
返回值类型：text。
示例：
| ``` 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串可以包含\n， 其中\n是1到9， 表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入， 并且它可以包含&表示应该插入匹配整个模式的子串。
可选的flags参数包含零个或多个改变函数行为的单字母标记。flags支持的选项值及含义描述如表3所示。
返回值类型：varchar。
示例：
| ``` 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支持的选项值及含义描述如表3所示。
此函数在ORA兼容模式数据库中且参数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" [, ...] ])
描述：以第一个参数为分隔符，链接第二个以后的所有参数。NULL参数被忽略。
NOTICE:
- 如果第一个参数值是NULL，会导致返回结果为NULL。
- 如果第一个参数值是空字符串（''），且数据库SQL兼容模式设置为ORA的情况下，会导致返回结果为NULL。这是因为ORA兼容模式将''作为NULL处理，避免此类行为，可以将数据库SQL兼容模式改为MySQL、TD或者PG。
返回值类型：text。
示例：
| ``` 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。
示例：
| ``` 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) gaussdb=# DROP TABLE test;
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -
convert(string bytea, src_encoding name, dest_encoding name)
描述：以dest_encoding指定的目标编码方式转换字符串string。src_encoding指定源编码方式，在该编码下，string必须是合法的。
返回值类型：bytea。
示例：
| ``` gaussdb=# SELECT convert('text_in_utf8', 'UTF8', 'GBK'); convert ---------------------------- \x746578745f696e5f75746638 (1 row)
| ------------------------------------------------------------------------------------------------------------------------------------------------------------ |  如果源编码格式到目标编码格式的转换规则不存在，则字符串不进行任何转换直接返回，如GBK和LATIN1之间的转换规则是不存在的，具体转换规则可以通过查看系统表pg_conversion获得。server_encoding为初始化数据库时指定。 示例： | ``` 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(expr, USING transcoding_name)
描述：接收一个参数，可以是字符也可以是数字，将其转换为一个字符编码为transcoding_name类型的字符串并返回。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT convert('asdas' using 'gbk'); convert --------- asdas (1 row)
| ------------------------------------------------------------------------------------------ |  此函数仅在MYSQL模式数据库中生效。 -
convert_from(string bytea, src_encoding name)
src_encoding指定源编码方式，在该编码下，string必须是合法的。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT convert_from('text_in_utf8', 'UTF8'); convert_from -------------- text_in_utf8 (1 row)
| ------------------------------------------------------------------------------------------------------------------ | -
convert_to(string text, dest_encoding name)
返回值类型：bytea。
示例：
| ``` 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。
示例：
| ``` gaussdb=# SELECT 'AA_BBCC' LIKE '%A@_B%' ESCAPE '@' AS RESULT; result -------- t (1 row)
| --------------------------------------------------------------------------------------------------- | | ``` gaussdb=# SELECT 'AA_BBCC' LIKE '%A@_B%' AS RESULT; result -------- f (1 row) ``` | | ---------------------------------------------------------------------------------------- | | ``` 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。
示例：
| ``` gaussdb=# SELECT regexp_like('ABC', '[A-Z]'); regexp_like ------------- t (1 row)
| -------------------------------------------------------------------------------------------- | | ``` gaussdb=# SELECT regexp_like('ABC', '[D-Z]'); regexp_like ------------- f (1 row) ``` | | -------------------------------------------------------------------------------------------- | | ``` gaussdb=# SELECT regexp_like('ABC', '[A-Z]','i'); regexp_like ------------- t (1 row) ``` | | ------------------------------------------------------------------------------------------------ | | ``` gaussdb=# SELECT regexp_like('ABC', '[A-Z]'); regexp_like ------------- t (1 row) ``` | | -------------------------------------------------------------------------------------------- | -
format(formatstr text [, str"any" [, ...] ])
返回值类型：text。
示例：
| ``` gaussdb=# SELECT format('Hello %s, %1$s', 'World'); format -------------------- Hello World, World (1 row)
| ---------------------------------------------------------------------------------------------------------------------------------- | -
md5(string)
描述：将string使用MD5加密，并以16进制数作为返回值。
MD5加密算法安全性低，存在安全风险，建议使用更安全的加密算法。
返回值类型：text。
示例：
| ``` gaussdb=# SELECT md5('ABC'); md5 ---------------------------------- 902fbdd2b1df0c4f70b4a5d23525e932 (1 row)
| ----------------------------------------------------------------------------------------------------------------------------------------------------- | -
sha(string) / sha1(string)
描述：将string使用SHA1加密，并以16进制数作为返回值，sha和sha1函数功能相同。
- SHA1加密算法安全性低，存在安全风险，不建议使用。
- 该函数仅在GaussDB兼容MY类型时（即sql_compatibility = 'MYSQL'）有效，其他类型不支持该函数。
返回值类型：text。
示例：
| ``` 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。
示例：
| ``` 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。
示例：
| ``` 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) -
find_in_set(text, set)
描述：查找给定成员在集合中的位置，从1开始计数。如果没有找到，返回0。分布式暂不支持SET数据类型，此函数执行时会报错。
返回值类型：int2。
-
find_in_set(str, strlist)
描述：查询字段strlist中是否包含str的结果，如有则返回str在strlist中的位置。输入为字符串str与strlist，其中str为用户需要查询的字符串，strlist是以“,”为分隔符，不同字符串通过分隔符组合在一起的字符串集合，strlist本身也为字符串。如果字符串str不在strlist 或者strlist为空字符串，则返回值为0。
参数：
参数说明 类型 描述 str text 目标字符串。 strlist text 字符串形式的集合。 返回值类型：int。
示例：
| ``` gaussdb=# SELECT find_in_set('ee','a,ee,c'); find_in_set ------------- 2 (1 row)
| ------------------------------------------------------------------------------------------------------ | -
encode(data bytea, format text)
返回值类型：text。
示例：
| ``` gaussdb=# SELECT encode(E'123\000\001', 'base64'); encode ---------- MTIzAAE= (1 row)
| ----------------------------------------------------------------------------------------------------- |
-
strcmp(expr1,expr2)
描述：根据当前字符序比较两个输入字符串，字符串相等返回0，第一个字符串小于第二个字符串返回-1，否则返回1。
参数说明：
参数说明 类型 描述 expr1/expr2 字符类型：CHAR、VARCHAR、NVARCHAR2、TEXT二进制类型：BYTEA数值类型：TINYING [UNSIGNED]、SMALLINT [UNSIGNED]、INTEGER [UNSIGNED]、BIGINT [UNSIGNED]、FLOAT4、FLOAT8、NUMERIC日期时间类型：DATE、TIME WITHOUT TIME ZONE、DATETIME、TIMESTAMPTZ 表示参与比较的字符串 返回值类型：INTEGER。
示例：
| ``` -- 切换MYSQL兼容模式数据库。 gaussdb=# CREATE DATABASE gaussdb_m dbcompatibility='MYSQL'; gaussdb=# \c gaussdb_m -- 设置兼容版本控制参数，开启常量字符串指定字符序功能。 gaussdb_m=# SET b_format_version='5.7'; gaussdb_m=# SET b_format_dev_version='s2'; gaussdb_m=# SELECT strcmp('abc', 'ABC'); strcmp -------- 0 (1 row) gaussdb_m=# SELECT strcmp('abc ', 'abc'); strcmp -------- 0 (1 row) gaussdb_m=# SELECT strcmp('1', 1); strcmp -------- 0 (1 row) gaussdb_m=# SELECT strcmp(123, 2); strcmp -------- -1 (1 row)
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |  - strcmp函数仅在sql_compatibility='MYSQL'时有效； - b_format_version='5.7'和b_format_dev_version='s1'版本开始，sql_mode参数"pad_char_to_full_length"控制对CHAR类型尾部填充空格，会影响strcmp的比较结果，具体请参见[表1](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0326.html#ZH-CN_TOPIC_0000001911585141__zh-cn_topic_0000001704458093_zh-cn_topic_0059777889_zh-cn_topic_0058966269_table29186418)； - b_format_version='5.7'和b_format_dev_version='s1'版本开始，字符类型、二进制类型、数值类型、日期时间类型行为兼容M，会影响strcmp的比较结果，具体请参见[数据类型](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0322.html#ZH-CN_TOPIC_0000001911585145)。对于数值类型中的浮点类型，由于连接参数设置不同，精度可能与M有差异，不建议使用该场景，或使用NUMERIC类型代替，具体请参见[连接参数](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0050.html#ZH-CN_TOPIC_0000001865747044__zh-cn_topic_0000001704458029_section51233666102514)； - b_format_version='5.7'和b_format_dev_version='s2'版本开始，支持字符转义和常量字符串获取字符序，字符序会影响strcmp的比较结果，具体请参见[SET](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0686.html#ZH-CN_TOPIC_0000001865586052)章节SET NAMES语法，字符类型不同字符序间的合并规则请参见[字符集和字符序合并规则](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0352.html#ZH-CN_TOPIC_0000001911666561)。
-
若字符串中存在换行符，如字符串由一个换行符和一个空格组成，在GaussDB中LENGTH和LENGTHB的值为2。
-
对于CHAR(n) 类型，GaussDB中n是指字符个数。因此，对于多字节编码的字符集， LENGTHB函数返回的长度可能大于n。
-
GaussDB支持多种类型的数据库，目前有4种，分别是ORA类型、MYSQL类型、TD类型以及PG类型。ORA的词法分析器与另外三种不一样，在ORA中空字符串会被当作是NULL。所以，当使用ORA类型的数据库时，假如上述字符操作函数中有空字符串作为参数，会出现没有输出的情况。例如：
gaussdb=# SELECT translate('12345','123',''); translate ----------- (1 row)这是因为内核在调用相应的函数进行处理前，会判断所输入的参数中是否含有NULL，假如有，则不会调用相应的函数，因此会没有输出。而在PG模式下，字符串的处理方式与postgresql保持一致，因此不会有上述问题产生。
扩展函数和操作符
-
pkg_bpchar_opc()
描述：扩展接口，用于新增bpchar和text或者text和bpchar策略比较操作符，为解决bpchar类型和text类型数据比较，无法命中索引问题。仅系统管理员可以安装扩展。
示例：
bpchar类型和text类型比较时（初始状态，前向兼容）:
| ``` /* 建表和数据初始化。 / gaussdb=# CREATE TABLE logs_nchar(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_nchar SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_nchar VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_nchar_logid on logs_nchar(log_id); gaussdb=# VACUUM ANALYZE logs_nchar; / 目的就是获得所有节点的详细执行计划。 / gaussdb=# SET max_datanode_for_plan = 1; 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..2539.01 rows=500 width=50) Filter: ((log_id)::text = 'FE306991300002 '::text) (8 rows) / 表logs_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) / 删除表。 */ gaussdb=# DROP TABLE logs_nchar;
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | bpchar类型和text类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）: | ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_nchar(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_nchar SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_nchar VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_nchar_logid on logs_nchar(log_id); gaussdb=# VACUUM ANALYZE logs_nchar; /* 系统管理员安装pkg_bpchar_opc扩展，数据库增加了bpchar和text类型比较操作符，以及索引相关内容。 */ gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION gaussdb=# SET max_datanode_for_plan = 1; 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: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log_id = rpad(btrim('FE306991300002 '::text), 16, ' '::text) Datanode Name: datanode1 [Bypass] Index Scan using idx_nchar_logid on logs_nchar (cost=0.00..8.27 rows=1 width=50) 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 | 002 (1 row) /* 删除表和扩展。 */ gaussdb=# DROP TABLE logs_nchar; gaussdb=# DROP EXTENSION pkg_bpchar_opc; ``` | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | text类型和bpchar类型比较时（初始状态，前向兼容）: | ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_text(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_text SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_text VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_text_logid on logs_text(log_id); gaussdb=# VACUUM ANALYZE logs_text; gaussdb=# SET max_datanode_for_plan = 1; 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: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_text WHERE log_id = 'FE306991300002 '::bpchar::text Datanode Name: datanode1 [Bypass] Index Scan using idx_text_logid on logs_text (cost=0.00..8.27 rows=1 width=38) 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 | 002 (1 row) /* 删除表。 */ gaussdb=# DROP TABLE logs_text; ``` | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）: | ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_text(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_text SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_text VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_text_logid on logs_text(log_id); gaussdb=# VACUUM ANALYZE logs_text; gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION gaussdb=# SET max_datanode_for_plan = 1; 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: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_text t1 WHERE log_id = 'FE306991300002 '::bpchar Datanode Name: datanode1 [Bypass] Index Scan using idx_text_logid on logs_text t1 (cost=0.00..8.27 rows=1 width=38) Index Cond: (log_id = 'FE306991300002 '::bpchar) (9 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* 删除表和扩展。 */ gaussdb=# DROP TABLE logs_text; gaussdb=# DROP EXTENSION pkg_bpchar_opc; ``` | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | hash join、text类型和bpchar类型比较时（初始状态，前向兼容）: | ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_varchar2(log_id varchar2, log_message text); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_varchar2; gaussdb=# CREATE TABLE logs_char(log_id char(16), log_message text); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_char; gaussdb=# SET max_datanode_for_plan = 1; 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 | 002 | FE306991300002 | 002 FE306991300003 | 003 | FE306991300003 | 003 FE306991300004 | 004 | FE306991300004 | 004 (3 rows) gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::bpchar = t2.log_id Datanode Name: datanode1 Hash Join (cost=1.07..2.14 rows=3 width=42) Hash Cond: ((t1.log_id)::bpchar = t2.log_id) -> Seq Scan on logs_varchar2 t1 (cost=0.00..1.03 rows=3 width=21) -> Hash (cost=1.03..1.03 rows=3 width=21) -> Seq Scan on logs_char t2 (cost=0.00..1.03 rows=3 width=21) (11 rows) gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 '; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300002 | 002 | FE306991300003 | 003 FE306991300002 | 002 | FE306991300004 | 004 (3 rows) /* 删除表。 */ gaussdb=# DROP TABLE logs_varchar2; gaussdb=# DROP TABLE logs_char; ``` | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | hash join、text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）: | ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_varchar2(log_id varchar2, log_message text); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_varchar2; gaussdb=# CREATE TABLE logs_char(log_id char(16), log_message text); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_char; gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION gaussdb=# SET max_datanode_for_plan = 1; SET /* 不推荐写法，由于t1表的log_id的varchar2类型，受安装扩展影响，隐式转换为text类型，和t2表的log_id比较，t2表的log_id类型会由char隐式转换为bpchar类型，此时log_id后面的空格会被数据库去掉，即'FE306991300002'='FE306991300002 '，所以不命中数据。 */ /* 错误示例： */ gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; log_id | log_message | log_id | log_message --------+-------------+--------+------------- (0 rows) gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::text = t2.log_id Datanode Name: datanode1 Hash Join (cost=1.07..2.14 rows=3 width=42) Hash Cond: ((t1.log_id)::text = t2.log_id) -> Seq Scan on logs_varchar2 t1 (cost=0.00..1.03 rows=3 width=21) -> Hash (cost=1.03..1.03 rows=3 width=21) -> Seq Scan on logs_char t2 (cost=0.00..1.03 rows=3 width=21) (11 rows) gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 '; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300002 | 002 | FE306991300003 | 003 FE306991300002 | 002 | FE306991300004 | 004 (3 rows) /* 推荐写法，避免t1表的log_id的数据类型转换成text类型，比较时空格被保留，和t2表的log_id比较无法命中数据，将t1表类型强转成没安装扩展前的bpchar类型，即'FE306991300002'='FE306991300002'，所以命中数据。 */ /* 正确示例： */ gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id::bpchar = t2.log_id; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300003 | 003 | FE306991300003 | 003 FE306991300004 | 004 | FE306991300004 | 004 (3 rows) /* 执行计划和没安装扩展前是一致的。 */ gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id::bpchar = t2.log_id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::bpchar = t2.log_id Datanode Name: datanode1 Hash Join (cost=1.07..2.14 rows=3 width=42) Hash Cond: ((t1.log_id)::bpchar = t2.log_id) -> Seq Scan on logs_varchar2 t1 (cost=0.00..1.03 rows=3 width=21) -> Hash (cost=1.03..1.03 rows=3 width=21) -> Seq Scan on logs_char t2 (cost=0.00..1.03 rows=3 width=21) (11 rows) gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 '; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300002 | 002 | FE306991300003 | 003 FE306991300002 | 002 | FE306991300004 | 004 (3 rows) /* 删除表和扩展。 */ gaussdb=# DROP TABLE logs_varchar2; gaussdb=# DROP TABLE logs_char; gaussdb=# DROP EXTENSION pkg_bpchar_opc; ``` | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |  - 为了解决bpchar类型（包含多个后补空格）与text类型做等值匹配操作的时候无法正常匹配数据以及索引问题。 - 涉及ubtree, btree，比较符号包含： >, >=, <, <=, <>。 - 影响面涉及字符类型之间的隐式转换，例如：变长和定长数据类型比较时，变长会优先转换为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支持比较函数。 | -
bpcharlikebpchar(BPCHAR, BPCHAR)
描述：判断第一个入参BPCHAR字符串是否LIKE第二个入参BPCHAR字符串。用于新增BPCHAR类型和BPCHAR类型的LIKE操作符。启用“~~”操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。
返回值类型：BOOL。
取值范围：
- t：表示两个BPCHAR类型参数匹配。
- f：表示两个BPCHAR类型参数不匹配。
示例：
| ``` gaussdb=# SELECT bpcharlikebpchar('455'::BPCHAR(10), '455 '::BPCHAR); bpcharlikebpchar ------------------ f (1 row) gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR(10)); bpcharlikebpchar ------------------ t (1 row) gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10)); bpcharlikebpchar ------------------ t (1 row) gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11)); bpcharlikebpchar ------------------ f (1 row) gaussdb=# CREATE TABLE op_test ( col BPCHAR(2) DEFAULT NULL ); CREATE TABLE gaussdb=# CREATE INDEX op_index ON op_test(col); CREATE INDEX gaussdb=# INSERT INTO op_test VALUES ('a'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('1'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('11'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('12'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('sd'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('aa'); INSERT 0 1 gaussdb=# SHOW behavior_compat_options; behavior_compat_options ------------------------- (1 row) --当behavior_compat_options不包含enable_bpcharlikebpchar_compare时，则未启用最新bpcharlikebpchar操作符，其bpchar匹配bpchar返回结果集非预期（正常应返回全部数据）。 gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; QUERY PLAN --------------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ (col)::text) (7 rows) gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; col ----- 11 12 aa sd (4 rows) gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare'; SET gaussdb=# SHOW behavior_compat_options; behavior_compat_options --------------------------------- enable_bpcharlikebpchar_compare (1 row) --开启参数后，将启用最新bpcharlikebpchar操作符，其匹配时返回行为符合预期行为。 gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; QUERY PLAN ------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col ~~ col) (7 rows) gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; col ----- 1 11 12 a aa sd (6 rows) gaussdb=# DROP TABLE op_test; DROP TABLE
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |  - 仅在数据库兼容性参数SQL_COMPATIBILITY为ORA时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段可以使用bpcharlikebpchar。 - 启用该特性，会影响BPCHAR类型进行LIKE模式匹配时的结果集与执行计划。 - SET behavior_compat_options=''; 表示关闭该特性，SET behavior_compat_options='enable_bpcharlikebpchar_compare';表示开启该特性。 - 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。 -
bpcharnlikebpchar(BPCHAR, BPCHAR)
描述：判断第一个入参BPCHAR字符串是否NOT LIKE第二个入参BPCHAR字符串。用于新增BPCHAR类型和BPCHAR类型的NOT LIKE操作符。启用“!~~”操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。
返回值类型：BOOL。
取值范围：
- t：表示两个BPCHAR类型参数匹配。
- f：表示两个BPCHAR类型参数不匹配。
示例：
| ``` gaussdb=# SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11)); bpcharnlikebpchar ------------------- t (1 row) gaussdb=# SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10)); bpcharnlikebpchar ------------------- f (1 row) gaussdb=# SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR); bpcharnlikebpchar ------------------- t (1 row) gaussdb=# CREATE TABLE op_test ( col BPCHAR(2) DEFAULT NULL ); CREATE TABLE gaussdb=# CREATE INDEX op_index ON op_test(col); CREATE INDEX gaussdb=# INSERT INTO op_test VALUES ('a'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('1'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('11'); INSERT 0 1 gaussdb=# insert into op_test VALUES ('12'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('sd'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('aa'); INSERT 0 1 gaussdb=# SHOW behavior_compat_options; behavior_compat_options ------------------------- (1 row) --当behavior_compat_options不包含enable_bpcharlikebpchar_compare时，则未启用最新bpcharnlikebpchar操作符，其BPCHAR匹配BPCHAR返回结果集非预期（正常应返回0条数据）。 gaussdb=# SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; col ----- 1 a (2 rows) gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; QUERY PLAN --------------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ (col)::text) (7 rows) gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare'; SET gaussdb=# SHOW behavior_compat_options; behavior_compat_options --------------------------------- enable_bpcharlikebpchar_compare (1 row) --开启参数后，将启用最新bpcharnlikebpchar操作符，其匹配时返回行为符合预期行为。 gaussdb=# SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; col ----- (0 rows) gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; QUERY PLAN ------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ col) (7 rows) gaussdb=# DROP TABLE op_test; DROP TABLE
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |  - 仅在数据库兼容性参数SQL_COMPATIBILITY为ORA时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段可以使用bpcharlikebpchar。 - 启用该特性，会影响BPCHAR类型进行NOT LIKE模式匹配时的结果集与执行计划。 - SET behavior_compat_options=''; 表示关闭该特性，SET behavior_compat_options='enable_bpcharlikebpchar_compare';表示开启该特性。 - 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。
更多详情请参考GaussDB 文档中心：doc.hcs.huawei.com/db/zh-cn/ga…
