MySQL - split string to json array (PHP explode equivalent)

mysql-str_split.sql

# MySQL 5.7.8 or later required
# https://dev.mysql.com/doc/refman/5.7/en/json.html
# Do not use it unless you know exactly what you're doing...
# For noobs - this function will not split your string to multiple rows


DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `STR_SPLIT`(`delimiter` VARCHAR(255), `input` LONGTEXT) RETURNS text CHARSET utf8
    DETERMINISTIC
BEGIN
	DECLARE element TEXT DEFAULT '';
	DECLARE output JSON DEFAULT '[]';
	
	IF NOT LOCATE(delimiter,input)>1 THEN 
		RETURN output; 
	END IF;

	foreach:WHILE TRUE DO
		SET element = SUBSTRING_INDEX(input, delimiter, 1);
		IF element REGEXP "^[0-9]+$" THEN
			SET output = JSON_ARRAY_APPEND(output, '$', element+0);
		ELSE
			SET output = JSON_ARRAY_APPEND(output, '$', element);
		END IF;
		IF(LOCATE(delimiter,input)) THEN
			SET input:=MID(input, LOCATE(',',input)+1);
		ELSE
			LEAVE foreach;
		END IF;
	END WHILE;
	
	RETURN output;
END//
DELIMITER ;