Re: mysql function returns a table

[Jason Wisdom]

Try this:

-- Put this at the beginning of your stored procedure
DECLARE intFoundPos INT;
DECLARE strElement VARCHAR(50); -- assuming any one value will not be longer than 50 chars...adjust as needed.

-- "strValues" is the string you want to split. Note it WILL get butchered so if you don't want that to happen, declare a new string.

-- Put this block wherever you would want to do the fnSplit
DROP TEMPORARY TABLE IF EXISTS tmpValues;
CREATE TEMPORARY TABLE tmpValues ( `values` VARCHAR(50) NOT NULL DEFAULT '' ) ENGINE = MEMORY;

SET intFoundPos = INSTR(strValues,',');
WHILE intFoundPos <> 0 do
SET strElement = SUBSTRING(strValues, 1, intFoundPos-1);
SET strValues = REPLACE(strValues, CONCAT(strElement,','), '');
INSERT INTO tmpValues (`values`) VALUES ( strElement);
SET intFoundPos = INSTR(strValues,',');
END WHILE;

IF strValues <> '' THEN
INSERT INTO tmpValues (`values`) VALUES (strValues);
END IF;

-- use tmpValues as your fnSplit table



This doesn't use a function...instead, you would copy this code inline to every needed occurance.

This assumes the delimiter is a comma, length of 1 character.

Also, this is a hack. I haven't tested it yet. But I will eventually need to do something similar and your post looked kind of time-urgent, so here's what I have so far.



Edited 1 time(s). Last edit at 05/30/2008 12:07AM by Jason Wisdom.