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;