字符截取函数substrb()、substr()及substring()的用法及差异

函数形式

substrb()、substr()和substring()均为字符串截取函数，都可带两个或三个参数，用于提取字符串中指定截取的开始位置和截取的长度。函数定义如下：

1
2
3

substrb(string, from [, count])
substr(string, from [, count])
substring(string, from [, count])

参数描述：从参数string中抽取子字符串，from表示抽取的起始位置，count表示抽取的字符串长度。

返回值类型：text

截取单位差异

• substrb()，按字节截取。
• substr()，按字符截取。
• substring()，按字符截取。

以utf8编码为例，1个汉字占3个字节，当使用substrb()截取长度3的子串时，只能截取到一个字符，而substr()/substring()可以截取到三个字符。

示例：

1
2
3
4

SELECT substrb('data数据库',3,5),substr('data数据库',3,5),substring('data数据库',3,5);
 substrb |  substr  | substring
---------+----------+-----------
 ta数    | ta数据库 | ta数据库

截取规则差异

GaussDB(DWS)目前支持三种兼容模式：ORA、TD和MySQL，在不同兼容模式下，函数差异具体如下：

• substrb()与ORA、TD和MySQL兼容模式行为一致。

  1	substrb(string, from [, count])

  从参数string中抽取子字符串，from表示抽取的起始位置，count表示抽取的字符串长度。

  可以表示为substrb(string, s[, n])：from的起始位置用s表示，count抽取的字符长度用n来表示。

  表1 substrb()与ORA、TD和MySQL兼容模式行为

  参数场景	ORA/TD/MySQL兼容	示例
  s > 0	从第s个字符位置开始，截取n个字节	1 2 3 4 5 select substrb('data数据库',5,3); substrb --------- 数 (1 row)
  s = 0	截取前n个字节	1 2 3 4 5 select substrb('data数据库',0,3); substrb --------- dat (1 row)
  s < 0	从倒数第|s|个字节位置开始，截取n个字节	1 2 3 4 5 select substrb('data数据库',-3,3); substrb --------- 库 (1 row)
  n > 0	截取n个字节	1 2 3 4 5 select substrb('data数据库',1,3); substrb --------- dat (1 row)
  n <= 0	空串(ORA模式输出null)	1 2 3 4 5 select substrb('data数据库',5,0),substrb('data数据库',5,-1); substrb | substrb ---------+--------- | (1 row)

• substr()与ORA、TD和MySQL兼容模式行为差异。

  1	substr(string, from [, count])

  从参数string中抽取子字符串，from表示抽取的起始位置，count表示抽取的字符串长度。

  可以表示为substr(string, s[, n])：from的起始位置用s表示，count抽取的字符长度用n来表示。

  s=0时存在兼容行为差异。

  表2 substr()与ORA、TD和MySQL兼容模式行为

  参数场景	ORA兼容	TD兼容	MySQL兼容
  s > 0	从第s个字符位置开始，截取n个字节	从第s个字符位置开始，截取n个字符	从第s个字符位置开始，截取n个字符
  s = 0	截取前n个字节 1 2 3 4 5 ora_db=> select substr('data数据库',0,3); substr -------- dat (1 row)	截取前n个字符 1 2 3 4 5 td_db=> select substr('data数据库',0,3); substr -------- dat (1 row)	空串 1 2 3 4 5 mysql_db=> select substr('data数据库',0,3); substr -------- (1 row)
  s < 0	从倒数第|s|个字节位置开始，截取n个字节	从倒数第|s|个字符位置开始，截取n个字符	从倒数第|s|个字符位置开始，截取n个字符
  n > 0	截取n个字节	截取n个字符	截取n个字符
  n <= 0	null	空串	空串

• substring()与ORA、TD和MySQL兼容模式行为差异。

  1	substring(string, from [, count])

  从参数string中抽取子字符串，from表示抽取的起始位置，count表示抽取的字符串长度。

  substring(string, s[, n])：from的起始位置用s表示，count抽取的字符长度用n来表示。

  s<=0和n<0时存在兼容行为差异。

  表3 substring()与ORA、TD和MySQL兼容模式行为

  参数场景	ORA兼容	TD兼容	MySQL 兼容
  s > 0	从第s个字符位置开始，截取n个字节	从第s个字符位置开始，截取n个字符	从第s个字符位置开始，截取n个字符
  s = 0	截取前n - 1个字节 1 2 3 4 5 ora_db=> select substring('data数据库',0,3); substring ----------- da (1 row)	截取前n - 1个字符 1 2 3 4 5 td_db=> select substring('data数据库',0,3); substring ----------- da (1 row)	空串 1 2 3 4 5 mysql_db=> select substring('data数据库',0,3); substring ----------- (1 row)
  s < 0	截取前s + n - 1个字节 1 2 3 4 5 ora_db=> select substring('data数据库',-1,3); substring ----------- d (1 row)	截取前s + n - 1个字节 1 2 3 4 5 td_db=> select substring('data数据库',-1,3); substring ----------- d (1 row)	从倒数第|s|个字符位置开始，截取n个字符 1 2 3 4 5 mysql_db=> select substring('data数据库',-1,3); substring ----------- 库 (1 row)
  n > 0	截取n个字节	截取n个字符	截取n个字符
  n = 0	null	空串	空串
  n < 0	报错 1 2 3 ora_db=> select substring('data数据库',3,-1); ERROR: negative substring length not allowed CONTEXT: referenced column: substring	报错 1 2 3 td_db=> select substring('data数据库',3,-1); ERROR: negative substring length not allowed CONTEXT: referenced column: substring	空串 1 2 3 4 5 mysql_db=> select substring('data数据库',3,-1); substring ----------- (1 row)