• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3396
  • Last Modified:

MySQL loop comma seperated values

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
Gary
Asked:
Gary
1 Solution
 
Ray PaseurCommented:
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
 
GaryAuthor Commented:
In a MySQL stored procedure
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your code looks like it shall work... what is the errors/issues you get?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
GaryAuthor Commented:
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
 
GaryAuthor Commented:
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
 
Tomas Helgi JohannssonCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now