DROP FUNCTION IF EXISTS `test`.`func_split_Total`$$
CREATE FUNCTION `func_split_Total` (f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER;
-------------------------------------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`func_split`$$
CREATE FUNCTION `test`.`func_split`
(
f_string varchar(1000),
f_delimiter varchar(5),
f_order int
) RETURNS varchar(255) CHARSET utf8
BEGIN
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 ;
-------------------------------------------------------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`INSERTTREE`$$
CREATE PROCEDURE `test`.`INSERTTREE`
(
IN f_string varchar(1000),
IN f_delimiter varchar(5)
)
BEGIN
declare count int default 0;
declare i int default 0;
set count = func_split_Total(f_string, f_delimiter);
DROP TABLE IF EXISTS `test`.`tmp_table`;
create temporary table `test`.`tmp_table`(`field` varchar(128)) DEFAULT CHARSET=utf8;
while i < count
do
set i = i + 1;
select func_split(f_string, f_delimiter, i);
#insert into `test`.`tmp_table`(`field`) values (func_split(f_string, f_delimiter, i));
end while;
END$$
DELIMITER ;
