Solved

MySQL loop comma seperated values

Posted on 2014-03-28
7
1,938 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 108

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39962916
your code looks like it shall work... what is the errors/issues you get?
0
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.

 
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 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 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 142

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 32
Install MySQL 5.6 and PHP on Centos Linux 6 54
simple mysql statement 3 11
Help with SQL field formatting 3 0
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The viewer will learn how to dynamically set the form action using jQuery.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now