功能如题,话不多说,直接上代码:
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_string_split` $$
CREATE PROCEDURE `sp_print_result`(
IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5)
)
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE tmp VARCHAR(50) DEFAULT '';
-- 获取分割后元素的个数cnt
SET cnt = 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));
DROP TABLE IF EXISTS tmp_print;
CREATE TEMPORARY TABLE tmp_print (tmp_elem VARCHAR(50) NOT NULL);
WHILE i < cnt
DO
SET i = i + 1;
-- 获取第i个元素
SET tmp = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,i)),f_delimiter,1));
IF LENGTH(tmp)>0 THEN
INSERT INTO tmp_print(tmp_elem) VALUES (tmp);
END IF;
END WHILE;
SELECT * FROM tmp_print;
END$$
DELIMITER ;
测试:
call sp_string_split('12;34;67;;89;123',';');结果如图所示:
tmp_elem
12
34
67
89
123