DELIMITER $$
DROP PROCEDURE IF EXISTS `SPLIT_SUB_STR0`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `SPLIT_SUB_STR0`(INOUT str VARCHAR(1000) , split0 VARCHAR(1) ,OUT split VARCHAR(20))
BEGIN
DECLARE c INT;
SET c=0;
SET c = LENGTH(SUBSTRING_INDEX(str,split0,1));
SET split = SUBSTRING(str, 1, c);
SET str = SUBSTRING(str, c + 2 );
END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `test`()
BEGIN
DECLARE c INT;
#目标字符串
SET @a = '1,,3,4,5,6,12';
# 分隔符
SET @c = ',';
# 存储风格后的字符串
SET @b = '';
REPEAT
# 调用上面的存储过程
CALL SPLIT_SUB_STR0(@a, ',', @c);
#将取得的字符串拼接,测试用
SET @b =CAST(@b AS SIGNED INTEGER) + CAST(@c AS SIGNED INTEGER) ;
#当目标字符串为空时,停止循环
UNTIL @a = ''
END REPEAT;
# 查看结果
SELECT @a, @c, @b;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `SPLIT_SUB_STR0`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `SPLIT_SUB_STR0`(INOUT str VARCHAR(1000) , split0 VARCHAR(1) ,OUT split VARCHAR(20))
BEGIN
DECLARE c INT;
SET c=0;
SET c = LENGTH(SUBSTRING_INDEX(str,split0,1));
SET split = SUBSTRING(str, 1, c);
SET str = SUBSTRING(str, c + 2 );
END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `test`()
BEGIN
DECLARE c INT;
#目标字符串
SET @a = '1,,3,4,5,6,12';
# 分隔符
SET @c = ',';
# 存储风格后的字符串
SET @b = '';
REPEAT
# 调用上面的存储过程
CALL SPLIT_SUB_STR0(@a, ',', @c);
#将取得的字符串拼接,测试用
SET @b =CAST(@b AS SIGNED INTEGER) + CAST(@c AS SIGNED INTEGER) ;
#当目标字符串为空时,停止循环
UNTIL @a = ''
END REPEAT;
# 查看结果
SELECT @a, @c, @b;
END$$
DELIMITER ;
antony
:antony1029@163.com
:http://antony1029.cnblogs.com