当字符串包含多个分隔符分割的字串时，在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)