1

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?

informatik01
  • 16,038
  • 10
  • 74
  • 104
Naren Gokal
  • 117
  • 8
  • 21
  • I'm not entirely sure what you mean by "NO COMMA FOUND". Could you clarify? Also, I think you want REPEAT instead of LOOP: http://dev.mysql.com/doc/refman/5.0/en/repeat-statement.html – acattle May 22 '12 at 06:35
  • This query makes no sense. What is exp1? Why do you want to insert a username multiple times into a separate table? Why does a username field contain CSVs? – Jivings May 22 '12 at 06:36
  • How do i use the REPEAT.. i want to loop through a string (comma separated), and insert this into a table. So, if there are 10 words, thats separated by a comma, 10 records must be inserted into the DB. Also, it must stop inserting into table when no comma is found – Naren Gokal May 22 '12 at 06:42
  • You should not store a comma separated list in the first place. If you normalize your table design, this cludge wouldn't be necessary –  May 22 '12 at 06:57
  • I know :-( .. but i need to add in what the user enters on screen, which will be a comma separated string. Im adding it to a temp table, so i can do a search on this, which will then be dropped. I just want to know how to loop through a comma separated string – Naren Gokal May 22 '12 at 07:02
  • Please provide the code that you have attempted and any errors you are receiving. – somnath May 22 '12 at 07:24
  • @Naren Gokal You may parse and insert records in the application (when user enters string). – Devart May 22 '12 at 07:50

1 Answers1

4

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;
Devart
  • 119,203
  • 23
  • 166
  • 186
  • If there are 5 words separated by comma in the string, 5 records need to be inserted into the table, with those 5 words. I need to LOOP through this string, if it exists – Naren Gokal May 22 '12 at 06:44
  • Sorry, I realise, that i do not know how many inserts there will be. In the above example, theres a fixed number = 3. I can have an unknown amount. How do i determine this ? – Naren Gokal May 22 '12 at 08:33
  • It does not matter. Just check the result query length. The size of query should not be more then `max_allowed_packet`. – Devart May 22 '12 at 08:51
  • Im lost :-( .. is the syntax correct in your example, cause i cant get it right .. table_name is a variable on my side as well, so im using @table_name – Naren Gokal May 22 '12 at 09:27
  • No problems. I have changed answer. You also can write 'SELECT @query' to view result query. – Devart May 22 '12 at 09:32
  • Hi Devart, I have table with 2 columns, myProcedure(col1value, 'col2valueX,col2valueY,col2valueZ' now I want to insert 3 records from that call, I hope you can easily work it out... – Abdul Wahab Jul 24 '15 at 13:10