proc.explode.mysql

DELIMITER $$
DROP FUNCTION IF EXISTS explode;
CREATE PROCEDURE `explode`(IN INPUT text,IN delim VARCHAR(10))
BEGIN
	DECLARE foundPos tinyint UNSIGNED;
	DECLARE tmpTxt text;
	DECLARE delimLen tinyint UNSIGNED;
	DECLARE element text;
	DROP TEMPORARY TABLE IF EXISTS tmpValues;
	CREATE TEMPORARY TABLE tmpValues
	(
		`values` text NOT NULL DEFAULT ''
	);
 
	SET delimLen = LENGTH(delim);
	SET tmpTxt = INPUT;
 
	SET foundPos = INSTR(tmpTxt,delim);
	WHILE foundPos <> 0 DO
		SET element = SUBSTRING(tmpTxt, 1, foundPos-1);
                SET tmpTxt = SUBSTRING(tmpTxt, foundPos+1, LENGTH(tmpTxt) - foundPos);
		INSERT INTO tmpValues (`values`) VALUES (element); 
		SET foundPos = INSTR(tmpTxt,delim);
	END WHILE;
 
	IF tmpTxt <> '' THEN
		INSERT INTO tmpValues (`values`) VALUES (tmpTxt);
	END IF;
	SELECT * FROM tmpValues;
END $$