Explode a string around a delimiter without a loop.
DROP PROCEDURE IF EXISTS explode;
DELIMITER |
CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)
BEGIN
DROP TABLE IF EXISTS temp_explode;
CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));
SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;
END |
DELIMITER ;
-- example call:
SET @str = "The quick brown fox jumped over the lazy dog";
SET @delim = " ";
CALL explode(@delim,@str);
SELECT id,word FROM temp_explode;
