文档首页/ 云数据库 GaussDB/ 开发指南（分布式_8.x）/ SQL参考/ 函数和操作符/ 字符处理函数和操作符
更新时间：2024-08-20 GMT+08:00
查看PDF
分享

字符处理函数和操作符

GaussDB提供的字符处理函数和操作符主要用于字符串与字符串、字符串与非字符串之间的连接，以及字符串的模式匹配操作。注意：字符串处理函数除了length相关函数，其他函数和操作符不支持大于1GBclob作为参数。

  • bin(number)

    描述：返回给定数字的二进制值表示的字符串。

    参数说明

    类型

    描述

    number
    • 整数类型：tinyint、smallint、mediumint、int、bigint。
    • 无符号整数类型：tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned
    • 字符和文本类型：char，varchar、tinytext，text，mediumtext，longtext，仅支持纯数字整数字符串，且整数范围在bigint范围内。
    • 浮点类型：float、real、double。
    • 定点类型：numeric、decimal、dec。
    • 布尔类型：bool。

    表示给定数字。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT bin(5);
   bin
--------
   101
(1 row)
    • bin函数仅在参数sql_compatibility='MYSQL'时生效。
    • 如果入参number是小数，将其向下取整。
    • 如果入参number的绝对值超过bigint unsigned最大值，将入参number转换为bigint unsigned最大值。
  • 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。

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。

  • elt(pos,str1,str2,...)

    描述：返回后面字符串的第pos个字符串。

    参数说明

    类型

    描述

    pos
    • 整数类型：tinyint、smallint、mediumint、int、bigint。
    • 无符号整数类型：tinyint unsigned、smallint unsigned、int unsigned。
    • 字符和文本类型：char，varchar、tinytext，text，mediumtext，longtext，仅支持纯数字整数字符串，且整数范围在bigint范围内。
    • 浮点类型：float、real、double。
    • 定点类型：numeric、decimal、dec。
    • 布尔类型：bool。

    表示指定位置的参数。

    str1,str2,...
    • 整数类型：tinyint、smallint、mediumint、int、bigint。
    • 无符号整数类型：tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • 字符和文本类型：char，varchar、tinytext，text，mediumtext，longtext。
    • 浮点类型：float、real、double。
    • 定点类型：numeric、decimal、dec。
    • 布尔类型：bool。
    • 大对象类型：tinyblob，blob，mediumblob，longblob。
    • 日期类型：datetime，timestamp，date，time。

    表示字符串列表。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT elt(3, 'a', 'b', 'c');
  elt
-------
     c
(1 row)
    • elt函数仅在参数sql_compatibility='MYSQL'时生效。
    • 如果入参pos小于1或者超过参数的数量，则返回NULL。
  • field(str,str1,str2,str3,...)

    描述：field函数返回str在{str1,str2,str3,...}列表中的位置，从1开始递增，返回0表示str未找到；str为NULL时，直接返回0；函数入参全部为数字时按照数字进行比较，入参全部为非数字类型时按照字符串进行比较，入参存在数字和非数字混合时按照double类型进行比较。

    参数说明

    类型

    描述

    str,str1,str2,str3,...
    • 整数类型：tinyint、smallint、mediumint、int、bigint、tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • Bool类型：true、false。
    • 浮点类型：float(p)、float、real、double，float(m, d)、double(m, d)、real(m, d)。
    • 定点类型：numeric、decimal、dec。
    • 文本类型：tinytext、text、mediumtext、longtext。
    • 字符串类型：char、varchar。
    • 大对象类型：tinyblob、blob、mediumblob、longblob。
    • 日期类型：datetime、timestamp、date、time。

    表示字符串列表。

    返回值类型：int。

    实例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT field( 'abc','1',1,'abc','abcd' );
 field
-------
     3
(1 row)
    • field函数仅在参数sql_compatibility='MYSQL'时生效。
    • b_format_version='5.7'和b_format_dev_version='s1'版本开始，sql_mode参数pad_char_to_full_length控制对char类型尾部填充空格，会影响field的比较结果，具体请参见表1。
    • b_format_version='5.7'和b_format_dev_version='s1'版本开始，字符类型、二进制类型、数值类型、日期时间类型行为兼容M，会影响field的比较结果，具体请参见数据类型。对于数值类型中的浮点类型，由于连接参数设置不同，精度可能与M有差异，不建议使用该场景，或使用numeric类型代替，具体请参见连接参数。
    • b_format_version='5.7'和b_format_dev_version='s2'版本开始，支持字符转义和常量字符串获取字符序，字符序会影响field的比较结果，具体请参见SET章节SET NAMES语法，字符类型不同字符序间的合并规则请参见字符集和字符序合并规则。
  • insert(str1, pos, len, str2)

    描述：返回字符串str1的处理结果，子字符串起始于pos位置，将len个字符长的字符串代替为str2。任意传参为NULL，则返回值为NULL。

    参数说明

    类型

    描述

    pos、len
    • 整数类型：tinyint、smallint、mediumint、int、bigint。
    • 无符号整数类型：tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • 字符和文本类型：char，varchar、tinytext，text，mediumtext，longtext，仅支持纯数字整数字符串。
    • 浮点类型：float、real、double。
    • 定点类型：numeric、decimal、dec。

    pos表示指定位置的参数；len表示替换的长度。

    str1、str2
    • NULL。
    • 整型：tinyint、smallint、mediumint、int、bigint; tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned；但整型数值超过正负81个9，返回值与MYSQL有差异。此差异为GaussDB与MYSQL固有差异，非本函数导致。
    • 浮点型、定点型：float、real、double；numeric、decimal、dec；但当以科学计数法表示时，GaussDB末尾0值会显示，M*不显示，以科学计数法打印，此为固有显示差异。非本函数所致。
    • 字符串类型：char、varchar。
    • 文本类型：tinytext、text，mediumtext、longtext。
    • 大对象类型：tinyblob、blob、mediumblob、longblob。
    • 日期类型：datetime、timestamp、date、time。

    字符串。

    返回值类型：函数返回值是text类型(s1和s2都是text类型)或bytea类型(s1或s2任意参数为bytea类型)。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT INSERT('abcdef',2,3,'gg');
 insert 
--------
 aggef
(1 row)
    • insert函数仅在参数sql_compatibility='MYSQL'时生效；
    • Int64类型传参有范围限制，当超出-9223372036854775808~9223372036854775807范围会直接报错。MYSQL对数值类型传参范围无限制，异常会告警，按照上限或下限数值处理；字符串传参有限制，入参text类型字符串长度最大为2^30-5字节，入参bytea类型字符串长度最大为2^30-512字节。
  • 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)
    • 此函数在ORA兼容模式数据库中且参数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。

  • locate(substr, str[, pos])

    描述：返回字符串substr在字符串str中第一次出现的位置，从位置pos（默认为1）开始算起。如果在str中找不到substr，则此函数返回0。

    参数说明

    类型

    描述

    substr、str
    • 整型：tinyint、smallint、mediumint、int、bigint、tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • 浮点型：float、double。
    • 任意精度类型：numeric。
    • 字符类型：char、varchar、text。
    • 二进制类型：bytea、blob。
    • 日期/时间类型：date、time、datetime、timestamp。
    1. substr必需，表示要搜索的子字符串。
    2. str必需，表示将被搜索的字符串。

    pos
    • 整型：tinyint、smallint、mediumint、int、tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • 浮点型：float、double。
    • 任意精度类型：numeric。
    • 字符类型：char、varchar、text。
    • 布尔类型：true、false。

    可选，表示搜索的起始位置。

    返回值类型：int。

    示例：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    gaussdb=# SELECT locate( 'b','abcabc');
 locate
-------
     2
(1 row)

gaussdb=# SELECT locate( 'b','abcabc',3);
 locate
-------
     5
(1 row)

    locate函数仅在参数sql_compatibility='MYSQL'时生效。

  • lpad_s(string text, length int [, fill text])

    描述：通过填充字符fill（缺省时为空白），把string填充为length长度。如果string已经比length长则报错。

    返回值类型：text。

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s1的情况下：
    • 参数length表示字符串显示长度，单个字符的显示长度按照ORA兼容处理。
    • 函数执行过程中出现length剩余长度为1且下一个字符显示长度为2，在字符串左侧添加一个空格字符。
    • 参数length入参若为小数则不会被四舍五入，而是会向下取整。
    • 参数string与fill不支持不符合编码规范的输入。
    在其他情况下：
    • 参数length表示字符串中字符总长度，单个字符的长度固定为1。
    • 参数length入参若为小数则被四舍五入。
    • 参数string与fill不支持不符合编码规范的输入。
  • lpad(string text, length int [, fill text])

    描述：通过填充字符fill（缺省时为空白），把string填充为length长度。如果string已经比length长则将其尾部截断。

    返回值类型：text。

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s1的情况下：
    • 参数length表示字符串显示长度，单个字符的显示长度按照ORA兼容处理。
    • 函数执行过程中出现length剩余长度为1且下一个字符显示长度为2，在字符串左侧添加一个空格字符。
    • 参数length入参若为小数则不会被四舍五入，而是会向下取整。
    • 参数string与fill不支持不符合编码规范的输入。
    在其他情况下：
    • 参数length表示字符串中字符总长度，单个字符的长度固定为1。
    • 参数length入参若为小数则被四舍五入。
    • 参数string与fill不支持不符合编码规的范输入。
  • make_set(bits,str1,str2...)

    描述：str1对应bits入参在比特形式下的位0，str2对应位1，以此类推。对应比特形式下位为1，则将对应的str参数追加到结果中，以逗号作为分隔符返回。

    参数说明

    类型

    描述

    make_set
    • bits入参支持：
      • NULL。
      • 整型：tinyint、smallint、mediumint、int、bigint; tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
      • 浮点、定点型：float、real、double；numeric、decimal、dec。
      • 比特类型：bit。Gauss未完善支持bit，代码设计中当入参1为十六进制如“x'FFF'”,长度为奇数时，MYSQL会报错，存在差异。
      • 字符串类型：char，varchar；仅支持纯数字整数字符串；带小数字符串由于取整规则与M*存在差异，返回结果存在差异。数字字母拼接字符串不支持。纯数字字符串范围在bigint范围。
      • 文本类型：tinytext，text，mediumtext，longtext；仅支持纯数字整数字符串，且整数范围在bigint范围内，超出范围返回结果与MYSQL存在差异；带小数字符串由于取整规则与MYSQL存在差异，返回结果存在差异。数字字母拼接文本类型不支持。纯数字字符串范围限制在bigint范围。
      • 大对象类型：tinyblob，blob，mediumblob，longblob
    • str可变入参支持：
      • NULL。
      • 整型：tinyint、smallint、mediumint、int、bigint; tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned；但整型数值超过正负81个9，返回值与MYSQL有差异。此差异为GaussDB与M*固有差异，非本函数导致。
      • 浮点型、定点型：float、real、double；numeric、decimal、dec；但当以科学计数法表示时，GaussDB末尾0值会显示，MYSQL不显示，以科学计数法打印，此为固有显示差异。非本函数所致。
      • 字符串类型：char，varchar
      • 文本类型：tinytext，text，mediumtext，longtext
      • 大对象类型：tinyblob，blob，mediumblob，longblob
      • 日期类型：datetime, timestamp, date, time

    返回结果由str1,str2…中被选中的某些字符串拼接而成，以逗号作为分隔符。

    返回值类型：text。

    示例：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    gaussdb=# SELECT make_set(7,'a','b','c','d');
 make_set
----------
 a,b,c
(1 row)
gaussdb=# SELECT make_set(b'1010101010101101010101010110101010101011010101010101101010101010','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','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69');
                                             make_set
---------------------------------------------------------------------------------------------------
 2,4,6,8,10,12,13,15,17,19,21,23,25,26,28,30,32,34,36,38,39,41,43,45,47,49,51,52,54,56,58,60,62,64
(1 row)
    • make_set函数仅在参数sql_compatibility='MYSQL'时生效。
    • bits参数为整型时，最大范围支持到int128，低于MYSQL范围。
    • bits参数为日期类型datetime、timestamp、date、time，由于时间类型转整型与MYSQL存在差异，非本函数所致，目前均不做支持。
    • bit类型或bool类型由于此类数据类型GaussDB与MYSQL存在差异，返回结果导致的差异为GaussDB与MYSQL固有差异,非本函数导致。bits入参为bool类型，str入参为bit类型与bool类型均不做支持。
  • 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，字符串不存在时返回0。

    示例：

    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(str)

    描述：返回字符串由单引号括起来，并为字符串中\、单引号'、ASCII NUL(\0)和Control+Z(\Z)的实例前面带上反斜线\。如果参数为NULL，则返回值为字符串NULL，而不括起单引号。

    参数说明

    类型

    描述

    quote
    • NULL。
    • 整型：tinyint、smallint、mediumint、int、bigint; tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned；但整型数值超过正负81个9，返回值与M*有差异。此差异为Gauss与M*固有差异，非本函数导致。
    • 浮点型、定点型：float、real、double；numeric、decimal、dec；但当以科学计数法表示时，Gaussdb末尾0值会显示，M*不显示，以科学计数法打印，此为固有显示差异。非本函数所致。
    • 字符串类型：char、varchar。
    • 文本类型：tinytext、text，mediumtext、longtext。
    • 大对象类型：tinyblob、blob、mediumblob、longblob。
    • 日期类型：datetime、timestamp、date、time。

    为输入字符串添加引号，并为反斜线\、单引号'前面加\。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT quote('hello\' world');
    quote
--------------
 'hello\' world'
(1 row)
    1. 函数仅在参数sql_compatibility='MYSQL'时生效；
    2. 开启GUC参数： 
      SET standard_conforming_strings=off;
SET escape_string_warning=off;
SET backslash_quote=on;
    3. 已知str字符串中含有\Z、\r、\%、\_，GaussDB未进行转义，与MYSQL存在差异。斜线后跟部分数字也会引起差异，如\563。由转义字符引起的本函数与MYSQL的差异，此为GaussDB与MYSQL的转义字符差异，与本函数无关。
    4. str字符串中的\b，输出结果表现形式与MYSQL有差异。此为GaussDB与MYSQL的固有差异，与本函数无关。
    5. str字符串中含有\0时，GaussDB由于UTF-8字符集不识别该字符，输入不成功。此为GaussDB与MYSQL的固有差异，与本函数无关。
    6. str为bit或bool类型时，由于GaussDB与MYSQL此类型目前有差异，暂不支持此类类型。
    7. GaussDB最大支持1GB数据传输，str入参长度最大支持536870908，函数返回结果字符串最大支持1GB。
  • quote_ident(string text)

    描述：返回适用于SQL语句的标识符形式（使用适当的引号进行界定）。只有在必要的时候才会添加引号（字符串包含非标识符字符或者会转换大小写的字符）。返回值中嵌入的引号都写了两次。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT quote_ident('hello world');
 quote_ident
--------------
 "hello world"
(1 row)
  • quote_literal(string text)

    描述：返回适用于在SQL语句里当作文本使用的形式（使用适当的引号进行界定）。

    支持显式转换成字符类型后的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)

    描述：返回适用于在SQL语句里当作字符串使用的形式（使用适当的引号进行界定）。

    支持显式转换成字符类型后的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)
  • space(count)

    描述：返回由指定数量的空格组成的字符串。

    参数说明

    类型

    描述

    count
    • 整数类型：tinyint、smallint、mediumint、int、bigint。
    • 无符号整数类型：tinyint unsigned、smallint unsigned、int unsigned。
    • 字符和文本类型：char，varchar、tinytext、text、mediumtext、longtext，仅支持纯数字整数字符串，且整数范围在bigint范围内。
    • 浮点类型：float、real、double。
    • 定点类型：numeric、decimal、dec。
    • 布尔类型：bool。

    表示空格数量。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT space(5);
 space
--------
     
(1 row)
    • space函数仅在参数sql_compatibility='MYSQL'时生效。
    • 如果入参count小于1073741819且大于0，返回count个空格字符串，否则返回空字符串。
  • 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)

    此函数在MYSQL模式数据库中，且GUC参数 b_format_version = '5.7'和b_format_dev_version = 's1'时[from int]可以为负数，此时表示从后往前数的第几个字符，不设置参数时[from int]为负数结果为空。

  • substring(string, pos, len)

    描述：截取子字符串，pos表示从第几个字符开始截取，len表示截取几个字节。

    参数：

    表1 参数说明

    参数说明

    类型

    描述

    取值范围

    string

    text

    需要被截取的字符串。

    -

    pos

    int

    需要被截取字符串的起点。

    绝对值小于字符串长度。

    len

    int

    需要被截取字符串的长度。

    大于0。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT substring('substrteststring', -5, 5);
 substring
-----------
 tring
(1 row)

    此函数在MYSQL模式数据库中，且GUC参数 b_format_version = '5.7'和b_format_dev_version = 's1'时pos可以为负数，此时表示从后往前数的第几个字符，不设置参数时pos为负数结果为空。

  • substring_index(str,split,index)

    描述：str为字符串，split为分隔符字符串，index为str字符串被split分隔的数量位置；此函数返回第index个被split分隔的str字符串的左边/右边（index为正数则是左边，反之右边）的所有内容。

    参数：如表2所示。

    表2 substring_index参数说明

    参数说明

    类型

    描述

    str

    text

    需要被截取的字符串。

    split

    text

    需要分割的目标字符串。

    index

    int

    指定第index个split字符的位置，若为正数取左边所有内容，为负数取右边所有内容。

    返回值类型：text。

    示例：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    gaussdb=# SELECT substring_index('Test1splitTest2splitTest3splitTest4', 'split', 2);
 substring_index 
-----------------
 Test1splitTest2
(1 row)

gaussdb=# SELECT substring_index('Test1splitTest2splitTest3splitTest4', 'split', -2);
 substring_index 
-----------------
 Test3splitTest4
(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 支持的选项值及含义描述如表3所示。
      表3 flags 支持的选项值

      选项

      描述

      '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选项表示“.”能够匹配换行符('\n')；flags未指定'n'选项时，“.”不会匹配换行符。
      • 其他情况下，'n'选项和'm'选项的含义一样。

      'p'

      部分新行敏感的匹配，影响'.'和方括号表达式，和新行敏感的匹配('m'或'n')一样，但是不影响^和$。

      'q'

      普通字符匹配。

      's'

      单行模式匹配，含义与'm'、'n'相反。

      't'

      紧凑模式匹配，空白符匹配自身。

      'w'

      逆部分新行匹配，与'p'含义相反。

      'x'

      宽松模式匹配，忽略空白符。

      返回值类型：text。

      示例：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
      		 
      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 支持的选项值及含义描述如表3所示。

    此函数在ORA兼容模式数据库中且参数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 支持的选项值及含义描述如表3所示。

    返回值类型：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');
 regexp_matches
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)
  • regexp_match(string text, pattern text [, flags text])

    描述：返回一个字符串数组，为POSIX正则表达式与字符串第一个匹配所得到的子串。如果pattern不匹配，该函数返回空行。如果模式不包含圆括号子表达式，则每一个被返回的行都是一个单一元素的文本数组，其中包括匹配整个模式的子串。如果模式包含圆括号子表达式，该函数返回一个文本数组，它的第n个元素是匹配模式的第n个圆括号子表达式的子串。

    flags参数为可选参数，不带flags默认使用紧语法匹配。flags参数包含零个或多个改变函数行为的单字母标记，支持的标记如下表所示。

    表4 flags参数说明

    选项

    描述

    'b'

    POSIX正则表达式变为BRE（基础正则表达式）。

    'c'

    大小写敏感的匹配。

    'e'

    POSIX正则表达式变为ERE（扩展正则表达式）。

    'i'

    大小写不敏感的匹配，即在匹配时不区分大小写。

    'n'

    新行敏感的匹配，将字符串视为多行，^和$匹配每行的开头和结尾，（.）不可以匹配换行。

    'm'

    新行敏感的匹配，将字符串视为多行，^和$匹配每行的开头和结尾，（.）不可以匹配换行，n的匹配模式一致。

    'p'

    部分新行敏感的匹配，单行的开头和结尾，（.）不能匹配换行符，^只能匹配第一行开头，$只能匹配最后一行末尾。

    'q'

    将正则表达式视为一个文本字符串，所有都是普通字符。

    's'

    非新行敏感匹配，即将字符串视为单行，^和$匹配单行的开头和结尾，此模式下，正则表达式中的点号（.）可以匹配任何字符，包括换行符。

    't'

    紧语法匹配。

    'w'

    逆部分新行敏感的匹配，正则表达式中^匹配每行的开头，$只能匹配最后一行的结尾，（.）可以匹配任何字符，包括换行符。

    'x'

    扩展语法匹配，忽略RE中的空白字符和注释。以下三种场景除外：

    • 前置了\的空白字符或#将被保留。
    • 方括号中的空白字符或#将被保留。
    • 在多字符符号里面不能出现空白和注释，例如“(?:”。

    如果参数中存在空字符串（''），且数据库兼容模式设置为ORA的情况下，会导致返回结果为NULL，原因是ORA兼容模式下空字符串将''作为NULL处理。

    返回值类型：setof text[]。

    示例：

    gaussdb=# SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
 regexp_match
--------------
 {bar,beque}
(1 row)
gaussdb=# SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
 regexp_match
--------------
 barbeque
(1 row)
gaussdb=# SELECT regexp_match('Learning #PostgreSQL', 'R', 'c');
 regexp_match
--------------

(1 row)
gaussdb=# SELECT regexp_match('hello world', 'h e l l o', 'x');
 regexp_match
--------------
 {hello}
(1 row)
  • 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。

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s1的情况下：
    • 参数length表示字符串显示长度，单个字符的显示长度按照O兼容处理。
    • 函数执行过程中出现length剩余长度为1且下一个字符显示长度为2，在字符串右侧添加一个空格字符。
    • 参数length入参若为小数则不会被四舍五入，而是被截断。
    • 参数string与fill不支持不符合编码规范的输入。
    在其他情况下：
    • 参数length表示字符串中字符总长度，单个字符的长度固定为1。
    • 参数length入参若为小数则被四舍五入。
    • 参数string与fill不支持不符合编码规范的输入。
  • 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(text,int)/substr(str FROM pos)

    描述：其中str为目标字符串，pos为字符串中的位置。输出str从pos位置开始到字符结束的字符串。

    返回值类型：text。

    示例：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    gaussdb=# SELECT substr('stringtest' from 4);
 substr  
---------
 ingtest
(1 row)

gaussdb=# SELECT substr('stringtest', 4);
 substr  
---------
 ingtest
(1 row)
  • substr(str FROM pos FOR len)

    描述：提取子字符串，第一个int表示提取的起始位置，第二个表示提取几位字符。pos可以是负数，如果为负数，则从后往前提取。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT substr('teststring' from 5 for 2);
 substr 
--------
 st
(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中字符的数目。当sql_compatibility = 'MYSQL'时，设置参数b_format_version = '5.7'、b_format_dev_version = 's1'后，string为字符串类型或文本类型时，返回string的字节数。

    返回值类型：integer。

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

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。

  • to_multi_byte(char)

    描述：将字符串中所有单字节字符转换为多字节字符。

    返回值类型：text。

    此函数在ORA兼容模式数据库中且参数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)

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下才支持使用。

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

    此函数在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。

    示例：

    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。

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。

  • asciistr(string)

    描述：把字符串string中非ASCII字符转换为\XXXX形式，其中XXXX表示UTF-16代码单元。

    返回值类型：varchar。

    此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。

  • unistr(string)

    描述：将字符串中的编码序列转换成对应字符，其他字符保持不变。

    返回值类型：text。

    • 此函数在ORA兼容模式数据库中且参数a_format_version值为10c和a_format_dev_version值为s2的情况下有效。
    • '\'后必须接4位16进制字符表示编码序列，或者接另一个'\'表示输入单个'\'字符。
    • 入参是时间类型时，时间类型会隐式转换成字符串类型。
  • vsize(expr)

    描述：返回输入表达式的字节数。

    返回值类型：int。

    此函数在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。

    示例：

    1
2
3
4
5
    		 
    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。

    示例：

     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。

    • 此函数在ORA兼容模式数据库中且参数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
19
20
21
22
23
    		 
    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]范围，会返回“?”。
    • 如需设置、修改字符集与字符序，请参考字符集与字符序章节。
  • regexp_substr(source_char, pattern)

    描述：正则表达式的抽取子串函数。SQL语法兼容ORA和MYSQL的情况下，GUC参数behavior_compat_options的值包含aformat_regexp_match时，“. ”不能匹配 '\n' 字符；不包含aformat_regexp_match时，“. ”能够匹配'\n'字符。

    返回值类型：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串可以包含\n， 其中\n是1到9， 表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入， 并且它可以包含\&表示应该插入匹配整个模式的子串。

    可选的flags参数包含零个或多个改变函数行为的单字母标记。flags支持的选项值及含义描述如表3所示。

    返回值类型：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)
  • regexp_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参数被忽略。

    • 如果第一个参数值是NULL，会导致返回结果为NULL。
    • 如果第一个参数值是空字符串（''），且数据库SQL兼容模式设置为ORA的情况下，会导致返回结果为NULL。这是因为ORA兼容模式将''作为NULL处理，避免此类行为，可以将数据库SQL兼容模式改为MySQL、TD或者PG。

    返回值类型：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
24
25
    		 
    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。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT convert('text_in_utf8', 'UTF8', 'GBK');
          convert        
----------------------------
 \x746578745f696e5f75746638
(1 row)

    如果源编码格式到目标编码格式的转换规则不存在，则字符串不进行任何转换直接返回，如GBK和LATIN1之间的转换规则是不存在的，具体转换规则可以通过查看系统表pg_conversion获得。server_encoding为初始化数据库时指定。

    示例：

     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(expr, USING transcoding_name)

    描述：接收一个参数，可以是字符也可以是数字，将其转换为一个字符编码为transcoding_name类型的字符串并返回。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT convert('asdas' using 'gbk');
 convert 
---------
 asdas
(1 row)

    此函数仅在MYSQL模式数据库中生效。

  • convert_from(string bytea, src_encoding name)

    描述：以数据库的编码方式转换字符串string。

    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)
  • 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。

    参数：

    表5 参数说明

    参数说明

    类型

    描述

    str

    text

    目标字符串。

    strlist

    text

    字符串形式的集合。

    返回值类型：int。

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT find_in_set('ee','a,ee,c');
 find_in_set 
-------------
           2
(1 row)
  • encode(data bytea, format text)

    描述：将二进制数据编码为文本数据。

    返回值类型：text。

    示例：

    1
2
3
4
5
    		 
    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。

    示例：

     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
    		 
    -- 切换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；
    • b_format_version='5.7'和b_format_dev_version='s1'版本开始，字符类型、二进制类型、数值类型、日期时间类型行为兼容M，会影响strcmp的比较结果，具体请参见数据类型。对于数值类型中的浮点类型，由于连接参数设置不同，精度可能与M有差异，不建议使用该场景，或使用NUMERIC类型代替，具体请参见连接参数；
    • b_format_version='5.7'和b_format_dev_version='s2'版本开始，支持字符转义和常量字符串获取字符序，字符序会影响strcmp的比较结果，具体请参见SET章节SET NAMES语法，字符类型不同字符序间的合并规则请参见字符集和字符序合并规则。
  • 若字符串中存在换行符，如字符串由一个换行符和一个空格组成，在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类型比较时（初始状态，前向兼容）: 
     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
37
38
39
40
41
42
43
    		 
    /*
建表和数据初始化。
*/
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保持一致）: 
     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
37
38
39
40
41
42
43
44
45
46
    		 
    /*
建表和数据初始化。
*/
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类型比较时（初始状态，前向兼容）: 
     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
37
38
39
40
41
    		 
    /*
建表和数据初始化。
*/
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保持一致）: 
     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
37
38
39
40
41
42
43
44
45
46
    		 
    /*
建表和数据初始化。
*/
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类型比较时（初始状态，前向兼容）: 
     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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
    		 
    /*
建表和数据初始化。
*/
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保持一致）: 
      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
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
    		 
    /*
建表和数据初始化。
*/
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）。
    表6 pkg_bpchar_opc支持的函数说明

    接口名称

    描述

    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类型参数不匹配。
    示例： 
     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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
    		 
    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类型参数不匹配。

    示例：

     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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
    		 
    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），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。
父主题： 函数和操作符