原作者：师庆栋

• 适用范围
• 问题概述
• substring_index功能示例
  • 返回左侧两个子串
  • 返回右侧两个子串
  • 当参数值大于子串个数时，返回所有子串
  • 参数为负数且绝对值大于子串个数时，返回所有子串
• 确认需求
• 生成函数
• 测试

适用范围

MySQL 迁移至MogDB

问题概述

目前MogDB中无MySQL中substring_index相同功能的函数

substring_index功能示例

返回左侧两个子串

mysql> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', 2);
+---------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com.cn', '.', 2) |
+---------------------------------------------+
| www.mysql                                   |
+---------------------------------------------+
1 row in set (0.00 sec)

返回右侧两个子串

mysql> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', -2);
+----------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com.cn', '.', -2) |
+----------------------------------------------+
| com.cn                                       |
+----------------------------------------------+
1 row in set (0.00 sec)

当参数值大于子串个数时，返回所有子串

mysql> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', 6);
+---------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com.cn', '.', 6) |
+---------------------------------------------+
| www.mysql.com.cn                            |
+---------------------------------------------+
1 row in set (0.00 sec)

参数为负数且绝对值大于子串个数时，返回所有子串

mysql> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', -6);
+----------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com.cn', '.', -6) |
+----------------------------------------------+
| www.mysql.com.cn                             |
+----------------------------------------------+
1 row in set (0.00 sec)

确认需求

针对以上功能，定义需求如下
参数值超范围时，返回所有子串
参数值正常时，返回指定子串

生成函数

CREATE OR REPLACE FUNCTION substring_index(p_str text,p_delim varchar2,p_count int) return text
AS
  v_array text[];
  v_len int;
  v_json_new json;
  v_pos int;
  v_count int;
BEGIN
  /*判断子串个数存入 v_len 中 */
  v_array := string_to_array(p_str,p_delim);
  v_len := v_array.count;
  
  if p_count = 0 THEN
    return '';
  elsif p_count >= 1 and p_count >= v_len THEN
    /*参数为正值，且长度大于等于子串个数时 返回所有子串*/
    return p_str;
  elsif p_count >= 1 THEN
    /*根据参数截取左侧子串*/
    v_pos := instr(p_str,p_delim,1,p_count);
    return substr(p_str,1,v_pos-1);
  elsif p_count <= -1 and p_count <= -v_len THEN
    /*参数为负值，且绝对值大于子串个数时，返回所有子串*/
    return p_str;
  elsif p_count <= -1 THEN
    /*根据参数，截取右侧子串*/
    v_count := v_len + p_count;
    v_pos := instr(p_str,p_delim,1,v_count);
    v_pos := v_pos + length(p_delim);
    return substr(p_str,v_pos);
  end if;
END substring_index;

测试

stone=> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', 2);
 substring_index 
-----------------
 www.mysql
(1 row)

stone=> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', -2);
 substring_index 
-----------------
 com.cn
(1 row)

stone=> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', -6);
 substring_index  
------------------
 www.mysql.com.cn
(1 row)

stone=> SELECT SUBSTRING_INDEX('www.mysql.com.cn', '.', 6);
 substring_index  
------------------
 www.mysql.com.cn
(1 row)