GBase 8a带分隔符字符串的切割子串函数SUBSTRING_INDEX

当字符串包含多个分隔符分割的字串时,在GBase 8a中可以通过SUBSTRING_INDEX行数,指定分隔符和出现次数,来切割获得子串。本文介绍该函数的语法和使用样例。

语法

SUBSTRING_INDEX(str,delim,count)

说明

返回字符串 str 中在第 count 个分隔符 delim 之前的子串。返回值不包括找到的分隔符。

  • str 当前的字符串
  • delim 分隔符
  • count 出现的次数,
    • 如果 count 是一个正数,从左面查找,返回从开头到出现次数位置间所有字符;
    • 如果 count 是负数,从右面查找,返回从出现位置到末尾的所有字符。

样例

正向查找

注意返回的是整个字串,且不包含最后出现的那个分隔符。如果count=0则返回空。

gbase> select substring_index('1,2,3,4,5,6,7,8',',',0);
+------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',0) |
+------------------------------------------+
|                                          |
+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select substring_index('1,2,3,4,5,6,7,8',',',1);
+------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',1) |
+------------------------------------------+
| 1                                        |
+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select substring_index('1,2,3,4,5,6,7,8',',',2);
+------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',2) |
+------------------------------------------+
| 1,2                                      |
+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select substring_index('1,2,3,4,5,6,7,8',',',3);
+------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',3) |
+------------------------------------------+
| 1,2,3                                    |
+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

反向查找

从末尾开始查找和计数。也不包括最后的分隔符。

gbase> select substring_index('1,2,3,4,5,6,7,8',',',-1);
+-------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',-1) |
+-------------------------------------------+
| 8                                         |
+-------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select substring_index('1,2,3,4,5,6,7,8',',',-2);
+-------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',-2) |
+-------------------------------------------+
| 7,8                                       |
+-------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select substring_index('1,2,3,4,5,6,7,8',',',-3);
+-------------------------------------------+
| substring_index('1,2,3,4,5,6,7,8',',',-3) |
+-------------------------------------------+
| 6,7,8                                     |
+-------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

混合使用,可以截取指定位置之间的字符串

比如截取位置3到位置4之间的。就是先截取正向4位置的,再截取反向4-3=1位置的。

gbase> select substring_index(substring_index('1,2,3,4,5,6,7,8',',',4),',',-1);
+------------------------------------------------------------------+
| substring_index(substring_index('1,2,3,4,5,6,7,8',',',4),',',-1) |
+------------------------------------------------------------------+
| 4                                                                |
+------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

如下是截取2到6之间的字符串

gbase> select substring_index(substring_index('1,2,3,4,5,6,7,8',',',6),',',2-6);
+-------------------------------------------------------------------+
| substring_index(substring_index('1,2,3,4,5,6,7,8',',',6),',',2-6) |
+-------------------------------------------------------------------+
| 3,4,5,6                                                           |
+-------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)