I have the function below which will separate comma delimited strings like 1,2,4-7 into
1
2
4-7
and count 3 records:
create or replace function str2tbl
(p_str in varchar2,
p_delim in varchar2 default ',',
p_count out number)
return myTableType
as
l_str long default p_str || p_delim;
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, p_delim );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+length(p_delim) );
p_count := p_count +1;
end loop;
return l_data;
end;
I would like to add the extra functionality so that it would split 4-7 to
4
5
6
7
and return the count on these too like below:
1
2
4
5
6
7
p_count = 6
Any suggestion on how to do it, please?
Thanks