文档首页/ 云数据库 GaussDB/ 参考（分布式_V2.0-10.x）/ SQL参考/ 函数和操作符/ 字符处理函数和操作符
更新时间：2026-10-21 GMT+08:00
查看PDF
分享

字符处理函数和操作符

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

若进行字符比较时，存在输入参数为非法字符，统一默认返回false。

  • bin(number)

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

    参数：如表1所示。

    表1 bin参数说明

    参数名称

    类型

    描述

    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。
    • 布尔类型：boolean。

    表示给定数字。

    返回值类型：text

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT bin(5);
   bin
--------
   101
(1 row)
    • 该函数仅在参数sql_compatibility='MYSQL'时生效。
    • 如果入参number是小数，将其向下取整。
    • 如果入参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返回对应的字符串。

    参数：如表2所示。

    表2 elt参数说明

    参数名称

    类型

    描述

    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。
    • 布尔类型：boolean。

    表示指定位置的参数。

    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。
    • 布尔类型：boolean。
    • 大对象类型：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)
    • 该函数仅在参数sql_compatibility='MYSQL'时生效。
    • 如果入参pos小于1或者超过参数的数量，则返回NULL。
  • field(str, str1, str2, str3,...)

    描述：field函数返回str在参数列表（str1,str2,str3,...）中的位置序号，位置计数从1开始递增。若未找到匹配项，则返回0；当str参数为NULL时，直接返回0。该函数根据入参的类型采用不同比较规则：

    • 当所有入参均为数字类型时，按照数值规则进行比较。
    • 当所有入参均为为非数字类型时，按照字符串规则进行比较。
    • 当所有入参存在数字和非数字混合时，按照double类型进行比较。

    参数：如表3所示。

    表3 field参数说明

    参数名称

    类型

    描述

    str, str1, str2, str3,...
    • 整数类型：tinyint、smallint、mediumint、int、bigint、tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • boolean类型：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。

    表示字符串列表。

    返回值类型：bigint

    示例：

    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。

    参数：如表4所示。

    表4 insert参数说明

    参数名称

    类型

    描述

    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值会显示，MySQL不显示，此为固有显示差异。非本函数所致。
    • 字符串类型：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)

    描述：该函数返回在第一个参数中，从第三个参数指定的起始位置开始，匹配到第二个参数第n次出现的位置（n为第四个参数的值）。其中第三个参数表示开始匹配的起始位置，第四个参数表示匹配的次数。

    返回值类型：int

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
 instr
-------
     6
(1 row)
  • instrb(text, text, int, int)

    描述：该函数返回在第一个参数中，从第三个参数指定的起始位置开始，匹配到第二个参数第n次出现的位置（n为第四个参数的值）。其中，第三个参数表示开始匹配的起始位置，第四个参数表示匹配的次数。与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)
  • lengthc(text/bpchar)

    描述：返回指定字符串的长度，以Unicode字符编码规则计算。

    返回值类型：int

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT lengthc('hello');
 lengthc 
---------
       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。

    参数：如表5所示。

    表5 locate参数说明

    参数名称

    类型

    描述

    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。
    • substr必需，表示要搜索的子字符串。
    • str必需，表示将被搜索的字符串。

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

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

    返回值类型：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参数追加到结果中，以逗号作为分隔符返回。

    参数：如表6所示。

    表6 make_set参数说明

    参数名称

    类型

    描述

    bits
    bits入参支持：
    • NULL。
    • 整型：tinyint、smallint、mediumint、int、bigint; tinyint unsigned、smallint unsigned、int unsigned、bigint unsigned。
    • 浮点、定点型：float、real、double；numeric、decimal、dec。
    • 比特类型：bit。GaussDB未完善支持bit，代码设计中当入参1为十六进制格式（如“x'FFF'”）且长度为奇数时，MySQL会报错，但GaussDB处理方式不同。
    • 字符串类型：char、varchar；仅支持纯数字整数字符串（范围在bigint内）；带小数的字符串由于取整规则与MySQL存在差异，返回结果存在差异。数字字母拼接字符串不支持。
    • 文本类型：tinytext、text、mediumtext、longtext；仅支持纯数字整数字符串（范围在bigint内），超出范围返回结果与MySQL存在差异；带小数的字符串由于取整规则与MySQL存在差异，返回结果存在差异。数字字母拼接文本类型不支持。
    • 大对象类型：tinyblob、blob、mediumblob、longblob。

    通过该参数所对应的二进制的每一位控制是否包含后续的str参数。

    str1, str2,...
    str可变入参支持：
    • 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值会显示，MySQL不显示，会以科学计数法打印，此为固有显示差异，非本函数所致。
    • 字符串类型：char、varchar。
    • 文本类型：tinytext、text、mediumtext、longtext。
    • 大对象类型：tinyblob、blob、mediumblob、longblob。
    • 日期类型：datetime、timestamp、date、time。

    根据bits参数的二进制位决定是否将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类型或boolean类型由于此类数据类型GaussDB与MySQL存在差异，返回结果导致的差异为GaussDB与MySQL固有差异,非本函数导致。bits入参为boolean类型，str入参为bit类型与boolean类型均不做支持。
  • notlike(bytea, bytea)、notlike(name, text)、notlike(text, text)

    描述：该函数为重载函数，其功能为比较两个输入参数是否为不同的数据类型。

    返回值类型：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)

    描述：在字符串str两端分别添加单引号后进行输出，同时可将str中的特殊字符进行转义，包括反斜线（\\）、单引号（\'）、ASCII NUL（\0）或Control+Z（\Z）。如果参数为NULL，则返回值为字符串NULL，而不添加单引号。

    参数：如表7所示。

    表7 quote参数说明

    参数名称

    类型

    描述

    str
    • 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值会显示，MySQL不显示，以科学计数法打印，此为固有显示差异。非本函数所致。
    • 字符串类型：char、varchar。
    • 文本类型：tinytext、text，mediumtext、longtext。
    • 大对象类型：tinyblob、blob、mediumblob、longblob。
    • 日期类型：datetime、timestamp、date、time。

    在字符串str两端分别添加单引号后进行输出，同时可将str中的特殊字符进行转义，包括反斜线（\\）、单引号（\'）、ASCII NUL（\0）或Control+Z（\Z）。

    返回值类型：text

    示例：

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

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

    参数：入参为一个字符串，将被当作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，加上引号作为文本。入参类型为anyelement。

    返回值类型：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)

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

    参数：如表8所示。

    表8 space参数说明

    参数名称

    类型

    描述

    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。
    • 布尔类型：boolean。

    表示空格数量。

    返回值类型：text

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT space(5);
 space
--------
     
(1 row)
    • space函数仅在参数sql_compatibility='MYSQL'时生效。
    • 如果入参count小于1073741819且大于0，返回count个空格字符串，否则返回空字符串。
    • 当入参类型为浮点类型或定点类型时，若参数值为非整数，此函数的行为是向下取整。
    • 当入参类型为布尔类型时，若参数值为true，则将其转换为1；若参数值为false，则将其转换为0。
  • 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)

    描述：截取子字符串。

    参数：如表9所示。

    表9 substring(string, pos, len)参数说明

    参数名称

    类型

    描述

    取值范围

    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)

    描述：此函数返回第index个被split分隔的str字符串的左侧或右侧的内容，若index为正数，则返回左侧内容；若为负数，则返回右侧内容。

    参数：如表10所示。

    表10 substring_index参数说明

    参数名称

    类型

    描述

    str

    text

    待处理的源字符串。

    split

    text

    分隔符字符串。

    index

    int

    分隔符字符串的位置索引（从1开始），正数表示左侧，负数表示右侧。

    返回值类型：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正则表达式模式包含任何圆括号，那么将返回匹配第一对子表达式（对应第一个左圆括号）中的文本。若需使用圆括号而不触发此行为，建议在整个表达式外层添加一对圆括号。
    • 该函数在sql_compatibility = 'MYSQL'，且GUC参数b_format_version = '5.7'、b_format_dev_version = 's1'时，其使用方式与substr(str FROM pos)一致，输出结果和行为也和substr(str FROM pos)函数相同。
  • 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)

    该函数在sql_compatibility = 'MYSQL'，且GUC参数b_format_version = 5.7'、b_format_dev_version = 's1'时，其使用方式与substr(str FROM pos FOR len)函数一致，输出结果和行为也和substr(str FROM pos FOR len)函数相同。

  • rawcat(raw, raw)

    描述：字符串拼接函数。

    返回值类型：raw

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT rawcat('ab','cd');
 rawcat
--------
 ABCD
(1 row)
  • regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]])

    描述：正则表达式的抽取子串函数。与substr功能相似，正则表达式出现多个并列的括号时，也全部处理。

    参数：

    • string：用于匹配的源字符串。
    • pattern：用于匹配的正则表达式模式串。
    • position：可选参数，表示从源字符串的第几个字符开始匹配，默认值为1。
    • occurrence：可选参数，表示抽取第几个满足匹配的子串，默认值为1。
    • flags：可选参数，包含零个或多个改变函数匹配行为的单字母标记。flags支持的选项值及含义描述如表11所示。
      表11 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支持的选项值及含义描述如表11所示。

    此函数在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支持的选项值及含义描述如表11所示。

    返回值类型：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参数包含零个或多个改变函数行为的单字母标记，支持的标记如下表所示。

    表12 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_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)
  • regexp_split_to_array(string text, pattern text [, flags text ])

    描述：用POSIX正则表达式作为分隔符，分隔string。和regexp_split_to_table函数相似，但本函数会将分隔结果以一个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)
  • 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表示字符串显示长度，单个字符的显示长度按照与Oracle数据库一致。
    • 函数执行过程中出现length剩余长度为1且下一个字符显示长度为2，在字符串右侧添加一个空格字符。
    • 参数length入参若为小数则不会被四舍五入，而是被截断。
    • 参数string与fill不支持不符合编码规范的输入。
    • 参数string有长度限制，若填充后大小超出1G会报错。
    在其他情况下：
    • 参数length表示字符串中字符总长度，单个字符的长度固定为1。
    • 参数string有长度限制，若填充后大小超出1G会报错。
    • 参数length入参若为小数则被四舍五入。
    • 参数string与fill不支持不符合编码规范的输入。
  • substrb(text, int, 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时，string中匹配to长度之外的from位置（即from中超出to长度的字符）所对应的字符将被删除。

    返回值类型：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('ABCDEF');
 lengthb 
---------
       7
(1 row)
  • substr(string, from)

    描述：从参数string中提取子字符串，from表示提取的起始位置：

    • from为0时，按1处理。
    • from为正数时，提取从from到末尾的所有字符。
    • from为负数时，提取字符串的后n个字符，n为from的绝对值。

    返回值类型：text

    示例：

    • 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中提取子字符串，count表示提取的子字符串长度，from表示提取的起始位置。

    • from为0时，按1处理。
    • from为正数时，提取从from开始的count个字符。
    • from为负数时，提取从倒数第n个开始的count个字符，n为from的绝对值。
    • count小于1时，返回null。

    返回值类型：text

    示例：

    • 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)函数功能一致，但是计算单位为字节。

    返回值类型：text

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT substrb('ABCDEF',-2);
 substrb 
---------
 EF
(1 row)
  • substrb(string, from, count)

    描述：该函数和substr(string, from, count)函数功能一致，但是计算单位为字节。

    返回值类型：text

    示例：

    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的第一个字符在数据库字符集中的十进制编码。

    返回值类型：bigint

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

  • asciistr(string)

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

    返回值类型：text

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

    返回值类型：text

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT replace('jack and jue','j','bl');
    replace     
----------------
 black and blue
(1 row)
  • concat(str1, str2)

    描述：将字符串str1和str2连接并返回。该函数会调用data type的输出函数，返回值不确定，导致优化器在生成计划时不能提前计算结果。如果对性能有要求，建议用 || 替代。

    • 在sql_compatibility = 'MYSQL'的情况下，参数str1或str2为NULL会导致返回结果为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
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支持类型可以转换为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的情况下，此函数功能为根据输入大小返回对应字符序列：当前数据库编码字符集为多字节编码字符集时返回值为1-4个字节；当前数据库编码字符集为单字节编码字符集时返回值为将输入值通过mod 256运算后得到的单个字节。否则功能为：若当前数据库编码字符集为UTF-8字符集，则将输入作为unicode编码并返回一个UTF-8字符，若当前数据库编码字符集为其他字符集则返回ASCII码字符。

  • nchr(cvalue int|bigint)

    描述：nchr(cvalue int|bigint)返回入参在国家字符集中对应的字符。国家字符集由GUC参数nls_nchar_characterset控制，仅支持AL16UTF16和UTF8：

    • 如果nls_nchar_characterset为AL16UTF16，入参超过两字节，会进行截断，保留低两位字节。
    • 如果nls_nchar_characterset为UTF8，入参超过三字节，会被当作0处理。

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

    参数：cvalue支持可以转换成int或bigint的类型，取值范围为[0, 2^32 - 1]（对应unsigned int的范围）。入参若有小数部分，小数部分会被去掉。

    返回值类型：NVARCHAR2

    • 函数的返回值字节长度与Oracle数据库不一致。
    • 函数返回值受限于数据库字符集，在不同的数据库字符集下，如果没有对应的UTF8转数据库字符集的映射表，或者映射表中不存在该入参对应的UTF8编码时，表明当前数据库字符集不支持该UTF8字符，此时该函数的返回结果与Oracle数据库不一致。
    • 当前数据库字符集不支持入参对应的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'字符。

    参数：

    • source_char：用于匹配的源字符串。
    • pattern：用于匹配的正则表达式模式串。

    返回值类型：text

    示例：

    1
2
3
4
5
    		 
    gaussdb=# SELECT regexp_substr('500 Hello World, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR";
  REGEXPR_SUBSTR   
-------------------
 , Redwood Shores,
(1 row)
  • regexp_replace(string, pattern, replacement [,flags ])

    描述：替换匹配POSIX正则表达式的子字符串。 未匹配pattern时，返回原始的string串。 当存在匹配时，返回的string串中的匹配子串将被replacement串替换。replacement串可以包含\n， 其中\n是1到9， 表明string串匹配模式里第n个圆括号子表达式的子串应该被插入， 并且它可以包含\&表示应该插入匹配整个模式的子串。

    参数：

    • string：用于匹配的源字符串。
    • pattern：用于匹配的正则表达式模式串。
    • flags：可选参数，包含零个或多个改变函数匹配行为的单字母标记。g表示将所有匹配的子字符串替换为指定内容，而非仅替换第一个匹配项。其他flags支持的选项值及含义描述如表11所示。

    返回值类型：text

    示例：

     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支持的选项值及含义描述如表11所示。

    此函数在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。
    • 如果第一个参数值是空字符串（''），且数据库兼容模式设置为ORA的情况下，会导致返回结果为NULL。这是因为ORA兼容模式将''作为NULL处理，为避免此类情况，建议将数据库兼容模式改为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'：大小写敏感。
    • 'm'：将source_string视为多行。

    若忽略match_parameter选项，默认为大小写敏感，“.”不匹配换行符，source_string视为单行。当输入不支持的match_parameter选项，会产生提示信息“INFO: illegal argument for function”，并返回结果false。

    返回值类型：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加密算法安全性低，存在安全风险，不建议使用。
    • 该函数仅在GaussDBMYSQL兼容模式下（即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函数会在日志中记录哈希的明文，因此不建议用户使用该函数加密密钥等敏感信息。
    • 该函数仅在GaussDBMYSQL兼容模式下（即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中或者strlist为空字符串，则返回值为0。

    参数：如表13所示。

    表13 find_in_set(str, strlist)参数说明

    参数名称

    类型

    描述

    str

    text

    str为用户需要查询的字符串。

    strlist

    text

    strlist是以“,”为分隔符，不同字符串通过分隔符组合在一起的字符串集合，strlist本身也为字符串。

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

    参数：如表14所示。

    表14 strcmp参数说明

    参数名称

    类型

    描述

    expr1/expr2
    • 字符类型：CHAR、VARCHAR、NVARCHAR2、TEXT。
    • 二进制类型：BYTEA。
    • 数值类型：TINYINT [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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
      		 
      /*
建表和数据初始化。
*/
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)

/*
索引的bpchar与text隐式转换。
*/
gaussdb=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';
CREATE DATABASE
gaussdb=# \c ora_compatible_db
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_compatible_db" as user "tom".
ora_compatible_db=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION
ora_compatible_db=# CREATE TABLE t1_1(c1 char(5), c2 int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_compatible_db=# ALTER TABLE t1_1 ADD CONSTRAINT t1_1_pkey PRIMARY KEY (c1) WITH (storage_type=USTORE);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t1_1_pkey" for table "t1_1"
ALTER TABLE
ora_compatible_db=# INSERT INTO t1_1 VALUES('abc  ', 0);
INSERT 0 1
ora_compatible_db=# CREATE TABLE t1_2(c1 varchar(5));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_compatible_db=# INSERT INTO t1_2 VALUES('abc  ');
INSERT 0 1
ora_compatible_db=# CREATE /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2);
  c1   
-------
 abc  
(1 row)
ora_compatible_db=# SET max_datanode_for_plan = 64;
SET
ora_compatible_db=# EXPLAIN CREATE /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2);
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Node/s: All datanodes

 Remote SQL: SELECT/*+ IndexScan(t1_1 t1_1_pkey)*/ c1 FROM public.t1_1 WHERE c2 = 0 AND (c1 IN (SELECT t1_2.c1 FROM public.t1_2))
 Datanode Name: dist1_datanode1
   Nested Loop  (cost=28.91..93.76 rows=4 width=24)
     ->  HashAggregate  (cost=28.91..30.91 rows=200 width=24)
           Group By Key: (t1_2.c1)::text
           ->  Seq Scan on t1_2  (cost=0.00..25.13 rows=1513 width=24)
     ->  Index Scan using t1_1_pkey on t1_1  (cost=0.00..0.30 rows=1 width=24)
           Index Cond: (c1 = (t1_2.c1)::text)
           Filter: (c2 = 0)

(13 rows)
/*
删除表和扩展。
*/
gaussdb=# DROP TABLE t1_1;
gaussdb=# DROP TABLE t1_2;
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）。
    表15 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配置项。

    返回值类型：boolean，其取值范围为：

    • 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模式匹配时的结果集与执行计划。
    • GUC参数behavior_compat_options中不含有enable_bpcharlikebpchar_compare字段，表示关闭该特性；GUC参数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配置项。

    返回值类型：boolean，其取值范围为：

    • 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模式匹配时的结果集与执行计划。
    • GUC参数behavior_compat_options中不含有enable_bpcharlikebpchar_compare字段，表示关闭该特性；GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段，表示开启该特性。
    • 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。
父主题： 函数和操作符

相关文档