?
Solved

MySQL loop comma seperated values

Posted on 2014-03-28
7
Medium Priority
?
2,821 Views
Last Modified: 2014-05-21
In a stored procedure I'm trying to loop the comma separated values.
So if I have - 50,55,57 - I can use

select SUBSTRING_INDEX("50,55,57", ',', 1);

to get the 50 easily enough, but how do I get the other numbers.

// Get the number of elements
SET CI_size = LENGTH(categories_id1) - LENGTH(REPLACE(categories_id1, ',', '')) + 1;

WHILE CI_size > 0 DO
SET str_CI_el = SUBSTRING_INDEX(str_CI, ',', -1);
SET str_CI = SUBSTRING_INDEX(str_CI_el, ',', CI_size - 1);
INSERT INTO product_categories1 (product_id,categories_id) VALUES (product_id1, str_CI);
SET CI_size = CI_size - 1;
END WHILE;

Open in new window

0
Comment
Question by:Gary
7 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39962835
If you want a single row inserted for each of the numbers it might be easier to do it with some PHP added in.

Something like this:

$arr = explode(',', $string_of_comma_separated_numbers);
foreach ($arr as $num)
{
    /* INSERT QUERY HERE USING $num */
}
0
 
LVL 58

Author Comment

by:Gary
ID: 39962843
In a MySQL stored procedure
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39962916
your code looks like it shall work... what is the errors/issues you get?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 58

Author Comment

by:Gary
ID: 39963022
I only get the first value in the list i.e. in this case 50 for every entry. So I end up with something like this
sku	CI
1	50
1	50
1	50

Open in new window

0
 
LVL 58

Author Comment

by:Gary
ID: 39963025
The whole caboodle is

DELIMITER $$
DROP PROCEDURE IF EXISTS sep_CI$$
CREATE PROCEDURE sep_CI()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE i INT;
DECLARE cursor_id CURSOR FOR SELECT tbl_products_id FROM products where enabled=99;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN cursor_id;
FETCH cursor_id INTO i;
REPEAT
CALL split_CI(i);
FETCH cursor_id INTO i;
UNTIL done END REPEAT;
CLOSE cursor_id;
END $$
DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS split_CI$$
CREATE PROCEDURE split_CI(i INT)
BEGIN
DECLARE categories_id1 VARCHAR(50);
DECLARE product_id1 VARCHAR(50);
DECLARE CI_size INT;
DECLARE str_CI_el VARCHAR(50);
DECLARE str_CI VARCHAR(50);
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;

SELECT sku, category_ids
INTO product_id1, categories_id1
FROM products WHERE tbl_products_id=i;

SET CI_size = LENGTH(categories_id1) - LENGTH(REPLACE(categories_id1, ',', '')) + 1;

WHILE CI_size > 0 DO
SET str_CI_el = SUBSTRING_INDEX(str_CI, ',', -1);
SET str_CI = SUBSTRING_INDEX(str_CI_el, ',', CI_size - 1);
INSERT INTO product_categories1 (product_id,categories_id) VALUES (product_id1, str_CI);
SET CI_size = CI_size - 1;
END WHILE;

END $$
DELIMITER ;

Open in new window

0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 39963950
Hi!

This procedure takes a string of csv values as well as the separator and produces a temporary table with the values and their positions.
CREATE PROCEDURE csv_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS test.csv_Explode;
  CREATE TEMPORARY TABLE test.csv_Explode(
    `pos` int unsigned NOT NULL auto_increment,
    `val` VARCHAR(255) NOT NULL,
    PRIMARY KEY  (`pos`)
  ) ENGINE=Memory COMMENT='Explode() results.';

  IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
    INSERT INTO csv_Explode SET val = @sHead;

  END WHILE;
  #Return the values to the caller. 
  SELECT * FROM csv_Explode;
END; $$
DELIMITER ;

Open in new window


Then simply call the function like this

CALL csv_explode(',','2,3,4,5,6,7,8,9,10,11,12,13');

or

CALL csv_explode(';','2,3,4;5,6,7;8,9,10;11,12,13');

Look at how the code traverses the csv string.
You can change your code to this logic and that would solve your problem and you can then change the temporary table to your product_categories1 table.

Regards,
   Tomas Helgi
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40014616
I will post my article reference to tell you which options you have, though limited for mysql:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question