varchar split function

17 Dec 2013

      Hi,

I am trying to implement a function that split a varchar and returns a
table with the splitted values.
this is what i have so far but i dont know how to complete the
implementation.

CREATE FUNCTION EV_SPLIT(stringtosplit varchar(255), chartospliton char)
RETURNS TABLE (id int, val varchar(255), hashval BIGINT)
BEGIN
--if(EV_TABLEEXISTS('tmp_evsplitres') = 0)
--then
-- create local temporary table tmp_evsplitres (id int, val varchar(255),
hashval BIGINT);
--end if;
 declare idx int, tmpVal varchar(255);
set idx = 0;
 --start transaction;
while (position(chartospliton in str)>0)
do
set tmpVal = substring(str, 0, position(',' in str)-1);
insert into tmp_evsplitres values(idx, tmpVal, hash(tmpVal));
set str = substring(str, position(',' in str), length(str));
set idx = idx + 1;
end while;
 insert into tmp_evsplitres values(idx, str, hash(str));
 RETURN TABLE (
select * from tmp_evsplitres
);
--commit;
END;

varchar split function

imad hajj chahine