Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL sp_split() splits a comma delimited string into a recordset & inserts it into target table or returns it
-- ----------------------------
-- Procedure structure for `sp_split`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_split`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_split`(IN toSplit text, IN target char(255))
BEGIN
# Temp table variables
SET @tableName = 'tmpSplit';
SET @fieldName = 'variable';
# Dropping table
SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tableName);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# Creating table
SET @sql := CONCAT('CREATE TEMPORARY TABLE ', @tableName, ' (', @fieldName, ' VARCHAR(1000))');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# Preparing toSplit
SET @vars := toSplit;
SET @vars := CONCAT("('", REPLACE(@vars, ",", "'),('"), "')");
# Inserting values
SET @sql := CONCAT('INSERT INTO ', @tableName, ' VALUES ', @vars);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# Returning record set, or inserting into optional target
IF target IS NULL THEN
SET @sql := CONCAT('SELECT TRIM(`', @fieldName, '`) AS `', @fieldName, '` FROM ', @tableName);
ELSE
SET @sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(`', @fieldName, '`) FROM ', @tableName);
END IF;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
;;
delimiter ;
@mmatrabie

This comment has been minimized.

Copy link

mmatrabie commented Dec 11, 2017

if i have text with comma and i want to insert to columns table with the number of the comma in the text

@mbourgon

This comment has been minimized.

Copy link

mbourgon commented May 4, 2018

THANK YOU. Just spent half an hour trying various options online, and this was the first one that actually compiled and worked straight out of the box. I'm trying to figure out, however, whether or not the results are guaranteed to be in order. I just made a modified version that adds ID as auto_increment, and changes the query out to order by it.
[...]
# Creating table
SET @Sql := CONCAT('CREATE TEMPORARY TABLE ', @tableName, ' (id int not null auto_increment, ', @fieldName, ' VARCHAR(1000), primary key(id))');
[...]
# Inserting values
SET @Sql := CONCAT('INSERT INTO ', @tableName, '(',@fieldname ,')',' VALUES ', @vars);
[...]
IF target IS NULL THEN
SET @Sql := CONCAT('SELECT TRIM(', @fieldName, ') AS ', @fieldName, ' FROM ', @tableName, ' order by id');
ELSE
SET @Sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(', @fieldName, ') FROM ', @tableName, ' order by id');
END IF;

@roosbau2910

This comment has been minimized.

Copy link

roosbau2910 commented Oct 10, 2018

How to add another column like foreign key?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.