1、 获得所有以“某个符号“分割的字符串的个数
DELIMITER $$
CREATE FUNCTION `f_get_split_string_total`(
f_string varchar(4000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
-- Get the total number of given string.
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER ;
2、 按分割取出字符串
DELIMITER $$
CREATE FUNCTION `f_get_split_string`(f_string varchar(4000),f_delimiter varchar(5),f_order int)
RETURNS varchar(255)
BEGIN
-- Get the separated number of given string.
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END$$
DELIMITER ;
3、 再写一个存储过程进行组合使用这两个函数,输入需要分割的字符串和分隔符,输出按某某符号分割后的数组
DELIMITER $$
CREATE PROCEDURE `p_print_result`(
IN f_string varchar(4000),IN f_delimiter varchar(5)
)
BEGIN
-- Get the separated string.
declare cnt int default 0;
declare i int default 0;
set cnt = f_get_split_string_total(f_string,f_delimiter);
drop table if exists tmp_print;
create temporary table tmp_print (varchar(255) int not null);
while i < cnt
do
set i = i + 1;
insert into tmp_print(num) values (f_get_split_string(f_string,f_delimiter,i));
end while;
select * from tmp_print;
END$$
DELIMITER;
4、 输入字符串进行测试。
call p_print_result("434,123,12,234,123,123",",");
可能执行的时候会报错:The user specified as a definer (‘root’@’%’) does not exist
解决方法如下:
执行 :grant all privileges on *.* to root@"%" identified by ".";
执行 :flush privileges;