Loop through a possible comma separated string in MySQL

Question

How can I loop through comma separated string in MySQL.

This is what I want:

 select exp from expertise where user_name = 'userName';

 if exp != null then
     LOOP
       INSERT into table T1(exp1) VALUES(exp)
     UNTIL NO COMMA FOUND
 END IF;

How can I do this?

Answer 1

Plan A:

Write a stored procedure that would parse the string and run INSERT statements.

Plan B:

Build the INSERT statement string from the 'exp' values, and use prepared statements to execute the statement.

---

example:

Suppose we have a string - 'apple,cherry,strawberry'. We have to generate an INSERT statement like this - INSERT INTO table_name VALUES('apple'),('cherry'),('strawberry')

-- build a query
SET @table_name = 'table1';
SET @words = 'apple,cherry,strawberry';
SET @query = CONCAT('INSERT INTO ', @table_name, ' VALUES(\'',  REPLACE(@words, ',', '\'),(\''), '\')');

-- run the query
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;