Split a string and set values ​​in a MySql procedure

Question

I have a situation where I have to pass a comma-separated string to a MySQL procedure and split this string and paste these values ​​as strings into the table.

As below

For example, if I passed the string "jhon, swetha, sitha" to the mysql procedure, then it should break this line with a comma and insert these values ​​as 3 entries into the table.

CREATE PROCEDURE new_routine (IN str varchar(30)) BEGIN DECLARE tmp varchar(10); DECLARE inc INT DEFAULT 0; WHILE INSTR(str, ',') DO SET tmp = SUBSTRING(SUBSTRING_INDEX(str,',',inc),LENGTH(SUBSTRING_INDEX(str,',',inc-1))+1),',',''); SET str = REPLACE(str, tmp, ''); //insert tmp into a table. END WHILE; END 

But this did not work a single solution.

Answer 1

You need to be a little more careful with your string manipulations. You cannot use REPLACE() for this because it will replace multiple occurrences by corrupting your data if one item in a comma-separated list is a substring of another item. INSERT() string function is better for this, should not be confused with the INSERT used to insert into the table.

 DELIMITER $$ DROP PROCEDURE IF EXISTS `insert_csv` $$ CREATE PROCEDURE `insert_csv`(_list MEDIUMTEXT) BEGIN DECLARE _next TEXT DEFAULT NULL; DECLARE _nextlen INT DEFAULT NULL; DECLARE _value TEXT DEFAULT NULL; iterator: LOOP -- exit the loop if the list seems empty or was null; -- this extra caution is necessary to avoid an endless loop in the proc. IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN LEAVE iterator; END IF; -- capture the next value from the list SET _next = SUBSTRING_INDEX(_list,',',1); -- save the length of the captured value; we will need to remove this -- many characters + 1 from the beginning of the string -- before the next iteration SET _nextlen = LENGTH(_next); -- trim the value of leading and trailing spaces, in case of sloppy CSV strings SET _value = TRIM(_next); -- insert the extracted value into the target table INSERT INTO t1 (c1) VALUES (_next); -- rewrite the original string using the `INSERT()` string function, -- args are original string, start position, how many characters to remove, -- and what to "insert" in their place (in this case, we "insert" -- an empty string, which removes _nextlen + 1 characters) SET _list = INSERT(_list,1,_nextlen + 1,''); END LOOP; END $$ DELIMITER ; 

Next, a table for testing:

 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

The new table is empty.

 mysql> SELECT * FROM t1; Empty set (0.00 sec) 

Call the procedure.

 mysql> CALL insert_csv('foo,bar,buzz,fizz'); Query OK, 1 row affected (0.00 sec) 

Please note that "1 row is affected" does not mean what you expect. This refers to the last insert we made. Since we insert one row at a time, if the procedure inserts at least one row, you always get the number of rows in 1; if the procedure does not insert anything, you will get 0 rows.

Did it work?

 mysql> SELECT * FROM t1; +----+------+ | id | c1 | +----+------+ | 1 | foo | | 2 | bar | | 3 | buzz | | 4 | fizz | +----+------+ 4 rows in set (0.00 sec)