文章会有【原创】或者【转载】标示,部分来自Google Baidu的学习结果 {Java/PHP/Python/Ruby/Go}

MySQL分割字符串函数【学习】  


sp_print_result:
DELIMITER $$

CREATE PROCEDURE `test`.`sp_print_result` (IN f_string varchar(1000) character set utf8,
IN f_delimiter varchar(5)
)
BEGIN
declare cnt int default 0;
declare i int default 0;
set cnt = split_string_total(f_string,f_delimiter);
set names utf8;
drop table if exists tmp_print;
create temporary table tmp_print (num varchar(100) not null) ENGINE=InnoDB DEFAULT CHARSET=utf8;
while i < cnt do
set i = i + 1;
insert into tmp_print(num) values (split_string(f_string,f_delimiter,i));
end while;
select * from tmp_print;
END

split_string:
DELIMITER $$

CREATE FUNCTION `test`.`split_string` (f_string varchar(1000)  character set utf8,
f_delimiter varchar(5),
f_order int)
RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(255) CHARSET utf8 default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END

split_string_total:
DELIMITER $$

CREATE FUNCTION `test`.`split_string_total` (f_string varchar(1000) character set utf8,
f_delimiter varchar(5)
) RETURNS int
BEGIN
return 1 + (char_length(f_string) - char_length(replace(f_string,f_delimiter,'')));
END