MySQL Forums
Forum List  »  Stored Procedures

Re: mysql function returns a table
Posted by: Jason Wisdom
Date: May 29, 2008 11:56PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
55942
March 03, 2006 12:41AM
14641
February 26, 2008 01:22AM
16535
February 28, 2008 03:59PM
13165
March 01, 2008 07:45AM
10160
March 02, 2008 12:07PM
Re: mysql function returns a table
10403
May 29, 2008 11:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.