字符串处理func

CREATE FUNCTION func_split(f_string text character set utf8 ,f_delimiter varchar(5),f_order int) RETURNS varchar(9999) BEGIN DECLARE result VARCHAR(9999) character set utf8 default ‘’; set result=REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1)); RETURN result; END ;

select func_split(‘a,ab,abc,abcd,abcde,abcdef’,’,’,3);

abc；

select func_split(‘a|ab|abc|abcd|abcde|abcdef’,’|’,4);

abcd；

CREATE DEFINER = zkf@localhost FUNCTION func_split_TotalLength(f_string text character set utf8 ,f_delimiter varchar(5)) RETURNS int(11) BEGIN RETURN 1+(LENGTH(f_string)-LENGTH(replace(f_string,f_delimiter,’’))); END ;

select func_split_TotalLength(‘a,ab,abc,abcd,abcde,abcdef’,’,’)； 6;

select func_split_TotalLength(‘a|ab|abc|abcd|abcde’,’|’); 5;

通常嵌套再循环中使用对每个结果操作。如下：

CREATE PROCEDURE D_AUTHORITY(IN d_authority_id varchar(20000),IN controlParameter varchar(8888)) BEGIN DECLARE cnt int DEFAULT 0; DECLARE i int DEFAULT 0; set cnt=func_split_TotalLength(d_authority_id,’,’); while i < cnt do set i=i+1; update db_role r set r.authority_ids = replace(r.authority_ids,CONCAT(func_split(d_authority_id,’,’,i),’,’),’’); update db_role r set r.authority_ids = replace(r.authority_ids,CONCAT(’,’,func_split(d_authority_id,’,’,i)),’’); update db_authority a set a.delete_authority = ‘1’ where a.authority_id=func_split(d_authority_id,’,’,i); end WHILE; END; ———————————————— from: 版权声明：本文为博主「大声笑」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_29573217/article/details/77233730