Skip to content

Instantly share code, notes, and snippets.

@DusanBrejka
Last active April 26, 2016 12:24
Show Gist options
  • Save DusanBrejka/e05777f1766c58c116e3ea8ca9db3a5e to your computer and use it in GitHub Desktop.
Save DusanBrejka/e05777f1766c58c116e3ea8ca9db3a5e to your computer and use it in GitHub Desktop.
MySQL - split string to json array (PHP explode equivalent)
# 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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment