delimiter //
create or replace procedure str2arrfnq(indata text,delimiter char(1))
returns array(record(val varchar(255)))
as
declare
delim_cnt int =length(indata)-length(replace(indata,delimiter,’’));
array_size int = delim_cnt + 1;
strs ARRAY(RECORD(val varchar(255)));
offset_start int=1;
q query(val varchar(255));
echo_str text;
begin
for i in 0 … delim_cnt
loop
if (i < delim_cnt) then
q= select substr(indata,offset_start,regexp_instr(indata,’,’,1,i+1) - offset_start);
strs=collect(q);
else
q= select substr(indata,offset_start);
strs=collect(q);
end if;
offset_start= regexp_instr(indata,’,’,1,i+1)+1;
end loop;
for j in strs
loop
echo select j.val strval;
echo_str=concat(echo_str,j.val);
end loop;
return strs;
end //
when I query I am able to see the value for the string I am passing, but when iam collecting into an array it’s not working. throwing an error strs is not available. Appreciate for providing a solution
call str2arrfnq(’“TEST1”,“TEST2”,“TEST3”,’,’);
the requirement is converting a given comma separated line into an array.