Procedure to loop through comma separated string is not working

Question

I have corrected the code with the help of answer given in stack overflow. I want to loop through the comma separated string of Ids but not able to do so. Below given procedure only updates first record and not updating other records. What corrections are required so that I can loop through comma separated string. This is the code for my SP

BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
   SET strIDs = '';
  END IF;

do_this:
LOOP
SET strLen = LENGTH(strIDs);

UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
SET strIDs = MID(strIDs, SubStrLen, strLen);

IF strIDs = NULL THEN
  LEAVE do_this;
END IF;
END LOOP do_this;
END

The code is as provided in the answers for this post.

I tried with find_in_set() function but it works only if I pass ids start from beginning and not working if I pass Ids randomly. This is my script for the table

CREATE TABLE `testtable` (
    `Id` int(11) DEFAULT NULL,
    `Status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of testtable
-- ----------------------------
INSERT INTO `testtable` VALUES ('1', 'O');
INSERT INTO `testtable` VALUES ('2', 'O');
INSERT INTO `testtable` VALUES ('3', 'O');
INSERT INTO `testtable` VALUES ('4', 'O');
INSERT INTO `testtable` VALUES ('5', 'O');

This is the stored procedure

BEGIN
  UPDATE TestTable SET status = 'C' WHERE Id = FIND_IN_SET(Id, strIDs);
END

strIds is varchar type.

Now try @strIDs='2'

score 21 · Answer 1 · answered Dec 05 '12 at 14:22

Maybe this post is a bit too old but I have try the code presented by Devart and it is not working for me.

With few modification, this version works for me :

DELIMITER $$

CREATE PROCEDURE procedure1(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = CHAR_LENGTH(strIDs);

    UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = '' THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

Explanations:

1) Why "+2" IN SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(strIDs, ',', 1)) + 2;

When you execute the MID function on the next line, string index begin with 1. If you have the following string '4500,2', with the Devart version, MID Looks like MID('4500,2',4,6) which is return ',2'.

So if you add 1 at the substring length, you are on the delimiter. It's not enough. So you add the length of the delimiter. Now it's good.

2) Why IF strIDs = '' THEN in the loop condition?

Because when you do MID you return a string even if this string are empty.

Devart procedure are patched ! Thank a lot for you're anwser :)

Devart · Accepted Answer · 2011-10-12T13:40:12.893

10

Try this one (syntax errors are fixed and without CAST function) -

DELIMITER $$

CREATE PROCEDURE procedure1(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = LENGTH(strIDs);

    UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = NULL THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

You can debug your procedure with Debugger for MySQL.

EDIT2:

I'd do it without procedure. Try to use FIND_IN_SET function, e.g. -

SET @strIDs = '1,2,3'; -- your id values
UPDATE TestTable SET status = 'C' WHERE FIND_IN_SET(id, @strIDs);

Or create a temp. table, fill it with id values and join these two tables in UPDATE statement.

edited Oct 12 '11 at 13:40

answered Oct 12 '11 at 09:48

Devart

119,203
23
166
186

Its not working..Error is shown atDECLARE StrLen. I am using Navicat 8 Lite for MySQl. Debug is not possible – Microsoft Developer Oct 12 '11 at 10:02
@Devart..Its working if I remove leading and trailing DELEMETER, But not looping if I pass '1,2,3' as string. I want to update status where Ids are 1,2,3 but it only updates for 1 – Microsoft Developer Oct 12 '11 at 10:11
@Devart..I did like this. BEGIN UPDATE TestTable SET status = 'C' WHERE Id = FIND_IN_SET(id, strIDs); but its not woking. It shows some syntax error END – Microsoft Developer Oct 12 '11 at 10:59
@Devart..Sorry but it works only if I pass IDs starting from very first ID in table and not working if I pass IDs randomly. – Microsoft Developer Oct 12 '11 at 12:59
Procedure's argument strIDs and @strIDs are different variables. And try to deal without procedure. – Devart Oct 12 '11 at 13:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4199/discussion-between-chirag-fanse-and-devart) – Microsoft Developer Oct 12 '11 at 13:30
The procedure doesn't work. The loop never ends. @Payou's answer below works like a charm. – jnovacho Dec 21 '13 at 16:55
The main - asked problem is fixed. The loop ends or does not end - in this case it depends on users data, but it is another question) – Devart Dec 23 '13 at 07:21
can I insert '1,12,34,34,3445' to single row values ? using single statement – Abdul Wahab Jul 24 '15 at 13:00
Surely the `IF strIDs = NULL THEN` works like a charm in helping the loop terminate... (Just saying). – VH-NZZ Sep 30 '15 at 14:40
And this too is wrong: `SET strIDs = MID(strIDs, SubStrLen, strLen);` – VH-NZZ Sep 30 '15 at 14:42
@okiharaherbst How to reproduce your situation? – Devart Dec 07 '15 at 07:23
This is incorrect. Why it is accepted? Common, really? `IF strIDs = NULL THEN`, what is this? But even `IF strIDs IS NULL THEN` doesn't work. It just never ends. Tried with `'a,b'`. – Giorgi Nakeuri Feb 20 '16 at 16:34
Infinite loop here. – Neurotransmitter Aug 02 '16 at 13:03

Neurotransmitter · Answer 3 · 2019-08-13T19:03:14.170

I didn't find any suitable method, so I ended up making my own. It's pretty simple.

PROCEDURE db.loop_through_array()
BEGIN

  DECLARE strIDs varchar(150) DEFAULT 'one,two,three';
  DECLARE element varchar(150);

  WHILE strIDs != '' DO

    SET element = SUBSTRING_INDEX(strIDs, ',', 1);      

    UPDATE TestTable SET status = 'C' WHERE Id = element;

    IF LOCATE(',', strIDs) > 0 THEN
      SET strIDs = SUBSTRING(strIDs, LOCATE(',', strIDs) + 1);
    ELSE
      SET strIDs = '';
    END IF;

  END WHILE;

END

Procedure to loop through comma separated string is not working

3 Answers3

Linked

Related