Split a string and loop through values in MySQL stored procedure

Question

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

For example if I passed 'jhon,swetha,sitha' string to MySQL stored 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 work. Any solutions please?

Kia Kaha · Accepted Answer · 2020-12-11 16:39:59Z

62

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)

edited Dec 11, 2020 at 16:39

Kia Kaha

1,8191 gold badge23 silver badges43 bronze badges

answered May 14, 2016 at 19:59

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nikhil Pingle Over a year ago

Very useful answer.

Kia Kaha Over a year ago

Greate procedure code but I believe instead of LENGTH should be used CHAR_LENGTH - otherwise depending on the charset encoding the string recalculation will be wrong as in my case with cyrilic chars. More info in this answer stackoverflow.com/a/16278899/6355024

Michael - sqlbot Over a year ago

@KiaKaha You're correct, the original version only worked with single-byte characters. Thank you for the edit.

Collectives™ on Stack Overflow

Split a string and loop through values in MySQL stored procedure

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related