how to use SUBSTRING_INDEX function in mysql procedure

Question

I have create a mysql Procedure. here its code

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a , b, d TEXT;
  DECLARE c INT Default 0;
  DECLARE cur1 CURSOR FOR SELECT id, school_id  FROM my_list;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  read_loop: LOOP
    FETCH cur1 INTO a, b;
    IF done THEN
      LEAVE read_loop;
    END IF;
     insertSchool: LOOP  
         SET c = c + 1;
         d = SUBSTRING_INDEX(b, ',', c);
       IF d = "" THEN
          LEAVE insertSchool;
       END IF;      
        INSERT INTO my_school (my_id, school_id) VALUES (a,b);
    END LOOP insertSchool;
  END LOOP;
  CLOSE cur1;
END

In this cur1 has school_id as string it contain school ids in comma separated. i want split these ids and store in different table. but this line d = SUBSTRING_INDEX(b, ',', c); shows the error. can anyone please provide solution how to use SUBSTRING_INDEX in procedure?

This is very similar to stackoverflow.com/questions/5928599/… — Neil Hampton
– Neil Hampton, Commented Aug 9, 2013 at 5:35

peterm · Accepted Answer · 2013-08-09 06:13:18Z

5

Your immediate problem is not with SUBSTRING_INDEX but rather with missing SET

Change

d = SUBSTRING_INDEX(b, ',', c);

to

SET d = SUBSTRING_INDEX(b, ',', c);
^^^

Now that will solve syntax error but you need to make quite a few changes to your code to make it work.

To get n-th element from a list you need to apply SUBSTRING_INDEX() twice

SUBSTRING_INDEX(SUBSTRING_INDEX(list, ',', n), ',', -1)

That being said your SP might look like

DELIMITER $$
CREATE PROCEDURE my_sp()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a, b, d VARCHAR(12);
  DECLARE c, m INT DEFAULT 0;

  DECLARE cur1 CURSOR FOR SELECT id, school_id  FROM my_list;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    IF done THEN LEAVE read_loop; END IF;
    SET c = 0;
    SET m = CHAR_LENGTH(b) - CHAR_LENGTH(REPLACE(b, ',', ''));

    insertSchool: LOOP
       SET c = c + 1;
       IF c > m + 1 THEN LEAVE insertSchool; END IF;
       SET d = SUBSTRING_INDEX(SUBSTRING_INDEX(b, ',', c), ',', -1);
       INSERT INTO my_school (my_id, school_id) VALUES (a, d);
    END LOOP insertSchool;

  END LOOP;
  CLOSE cur1;
END$$
DELIMITER ;

Here is SQLFiddle demo

edited Aug 9, 2013 at 6:13

answered Aug 9, 2013 at 5:38

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

peterm Over a year ago

@naveengoyal Did it help? Do you need more help with your question? If it was what you were looking for please, consider to accept the answer.

Collectives™ on Stack Overflow

how to use SUBSTRING_INDEX function in mysql procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related