[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MySQL loop comma seperated values

Posted on 2014-03-28
7
Medium Priority
?
2,675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 25

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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

656 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