Split a string and loop through values in MySQL stored procedure
<p>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.</p>
<p>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.</p>
<pre><code>  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
</code></pre>
<p>But this does not work. Any solutions please?</p>