call dbo.sa_make_object('procedure','sa_split_list','dbo')
go

alter procedure dbo.sa_split_list( in str long varchar, in delim char( 10 ) default ',', in maxlen int default  0  )
result( line_num int, row_value long varchar )
begin
    declare pos int;
    declare startpos int;
    declare delimlen int;
    declare local temporary table tresult(
	line_num int primary key default autoincrement,
	row_value long varchar
    ) in SYSTEM not transactional;
    set startpos =  1 ;
    set delimlen = length( delim );
    if delimlen >  0  then
	lp: loop
	    set pos = locate( str, delim, startpos );
	    if (pos is null) or (pos =  0 ) then 
			leave lp 
	    end if;
	    if maxlen <>  0  and (pos - startpos) > maxlen then
			set pos = startpos + maxlen;
	    end if;
	    insert into tresult( row_value )
		select substr( str, startpos, pos - startpos );
	    set startpos = pos + delimlen;
	end loop;
	if length( str ) >= startpos then
	    insert into tresult( row_value ) values( substr( str, startpos ) );
	end if;
    end if;
    select line_num, row_value 
    from tresult
    order by line_num;
end
go

grant execute on dbo.sa_split_list to PUBLIC
go