Split a string and loop through values in MySql Procedure

Question

I got a situation where I have to pass a comma separated string to MySQL procedure and split that string and insert those values as rows in to a table.

As show below

For example if I passed 'jhon,swetha,sitha' string to mysql procedure then it have to split that string by comma and insert those values as 3 records into a 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 does not worked any solution please.

Answer 1

You'll need to be a little more careful with your string manipulation. You can't use REPLACE() for this, because that will replace multiple occurrences, corrupting your data if one element in the comma-separated list is a substring of another element. The INSERT() string function is better for this, not to be confused with the INSERT statement used for inserting into a 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 CHAR_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 = CHAR_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 (_value);    -- 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) 

Note the "1 row affected" does not mean what you would expect. It refers to the last insert we did. Since we insert one row at a time, if the procedure inserts at least one row, you'll always get a row count of 1; if the procedure inserts nothing, you'll get 0 rows affected.

Did it work?

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