Mysql 字符串截取函数

字符串截取 字段中往往记录同一类型的多个数据，有时候需要挨个去进行计算，如何拆分出每一个数据是非常有用的，比如在存储过程中循环取很方便。

CREATE DEFINER=`root`@`%` FUNCTION `StringSub`(str varchar(4000),sp1 varchar(100),sp2 varchar(1000)) RETURNS varchar(4000) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
    DETERMINISTIC
BEGIN
  #Routine body goes here...
    DECLARE index1 int;
    DECLARE index2 int;
    DECLARE length1 int;
    DECLARE length2 int;
    DECLARE str2 varchar(4000);

    if StringNULL(str) is null then 
        return null;
    end if;
    
--  if sp1 is not null and sp2 is not null then
        set index1 = 1;
        set index2 = LENGTH(str);
        set length1 = 0;
        set length2 = 0;
        
        if StringNULL(sp2) is not null then 
            set length2 = CHAR_LENGTH(sp2);
        end if;
        if StringNULL(sp1) is not null then 
            set length1 = CHAR_LENGTH(sp1);
        end if;
        
        if StringNULL(sp2) is not null and locate(sp2,str) > 0 then
            set index1 = locate(sp2,str)+length2;
        end if;
        
        set str2 = SUBSTR(str ,index1,index2);
        if StringNULL(str2) is null then
            return null;
        end if;
        
        if StringNULL(sp1) is not null and  locate(sp1,str2) = 1 then
            set index1 = locate(sp2,str)+length2+length1;
        end if;
        set str2 = SUBSTR(str ,index1,index2);
        if StringNULL(str2) is null then
            return null;
        end if;
        
        if  locate(sp1,str2) > 1 then
            set index2 = locate(sp1,str2);
        end if;

        set str2 = SUBSTR(str2 ,1,index2-1);
        return StringNULL(str2);
--  end if;
    

END

image.png