mysql split string

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

mysql split string

杨粼波 2018-11-06 17:40:22 浏览370 评论0

摘要: DROP PROCEDURE IF EXISTS SPLIT_STRING; DELIMITER // CREATE PROCEDURE SPLIT_STRING ( IN fullstr VARCHAR(1024), IN delim VARCHAR(1) ) SQL SECURI.

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, ';');

【云栖快讯】一站式开发者服务,海量学习资源免费学  详情请点击

网友评论