Split function in DB2 Version 5 Release 4

[vidhyar]

Hi,

I am new to Bytes and this is my first thread.

I know SQL Server but new to DB2. In my office we use IBM iSeries for Windows DB2 v5r4. I have written a function to split the string and return them as a table.

To be more clear in my requirement I will be parsing a string like
'ICECREAM (6) ~ CHOCOLATE (5) ~ PASTRY (2) ~' and I should get the particular value using the delimiter and the priority nos.( which is 6,5,3,2,1). Say in the above example I should display ICECREAM (6). This should be taken based on the priority nos.

I thank you in advance who could solve me out this issue.

My function:--


DROP FUNCTION SPLIT;

CREATE FUNCTION SPLIT(STRING VARCHAR(8000), DELIMITER CHAR(1))
RETURNS TEMPTABLE TABLE (ITEMS VARCHAR(8000), PRIORITY INTEGER)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN

DECLARE IDX INT;
DECLARE SLICE VARCHAR(8000);

IF LENGTH (STRING) >= 1 OR STRING <> '' THEN
SET IDX = 1;
END IF;

WHILE IDX <> 0

SET IDX = LOCATE (DELIMITER, STRING);

IF IDX <> 0 THEN
SET SLICE = LEFT (STRING, IDX-1);
ELSE
SET SLICE = STRING;
END IF;

IF LENGTH (SLICE) > 0 THEN
INSERT INTO TEMPTABLE (ITEMS, PRIORITY) VALUES (SLICE, LEFT(RIGHT(TRIM(SLICE), 2),1));
END IF;

SET STRING = RIGHT(STRING, LENGTH(STRING) - IDX);

IF LENGTH (STRING) = 0 THEN
BREAK;
END IF;

END WHILE;

RETURN (SELECT ITEMS, PRIORITY FROM TEMPTABLE);

END;

Thanks
Vidhya