MySQL Stored Procedure "explode()" Reg-Exp

Hi everyone,

I am wondering if someone can help me out with MySQL Procedures. One of the inputs to the procedure is a string made up of numbers separated by commas; e.g. "1,2,3,4,5,6,7,8".

Is there a way to explode the string into an "array" or at least loop through them? The idea is a split() equivalent functionality in a MySQL Stored Procedure.

Thanks for your time,

Haitham

#444386

Why not grab it with PHP, and then use PHP functions to take care of it?

-NC

#444392

The main reason is to do just a single MySQL call to the procedure increasing the performance when dealing with many changes. Having PHP to perform 100 Queries for each item - is much slower when you do it in a Procedure using via a single call.

After a while I came up with something that works pretty well. Here it is in case anyone is interested:

BEGIN

  declare cs varchar(8000);

  declare i int default 0;

  SET cs = '1,43,6,7,8,9,23,131';

  WHILE LENGTH(cs) > 0 DO

	   SET i = LOCATE(',', cs);

	   IF (i = 0)

		   THEN SET i = LENGTH(cs) + 1;

	   END IF;

#

# this prints the -id-

#

	   SELECT SUBSTRING(cs, 1, i - 1);

#

#

#

	   SET cs = SUBSTRING(cs, i + 1, LENGTH(cs));

  END WHILE;

END

#444510