mysql split string

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql split string

杨粼波 2018-11-06 17:40:22 浏览1029
展开阅读全文
DROP PROCEDURE IF EXISTS SPLIT_STRING;
DELIMITER //

CREATE PROCEDURE SPLIT_STRING (
	IN fullstr VARCHAR(1024),
    IN delim VARCHAR(1)
)
SQL SECURITY INVOKER
BEGIN

DECLARE inipos INTEGER;
DECLARE endpos INTEGER;
DECLARE maxlen INTEGER;
DECLARE item VARCHAR(1024);

SET inipos = 1;
SET fullstr = CONCAT(fullstr, delim);
SET maxlen = LENGTH(fullstr);

REPEAT
    SET endpos = LOCATE(delim, fullstr, inipos);
    SET item =  SUBSTR(fullstr, inipos, endpos - inipos);

    IF item <> '' AND item IS NOT NULL THEN           
        SELECT item;
    END IF;
    SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;

END//
DELIMITER ;


SET @agg = "G1;G2;G3;G4;" ;
call SPLIT_STRING(@agg, ';');

网友评论

登录后评论
0/500
评论
杨粼波
+ 关注