String Split in Oracle

With all the wonders Oracle PL/SQL can produce at some moment in the past I could not find a suitable string split function. When majority of other programming languages have this functionality available, PL/SQL somehow was left behind. I spent a couple of hours mostly evaluating different data scenarios and wrote this function. Later it received some additions in the form of the flexible delimiter length, and not just a single token. This function proved to be very efficient and worked just well for me and my colleagues on various projects. I decided to offer it to whoever might need it.

If you are at the beginner level of Oracle PL/SQL, then below is a short example on how to call GetStrings function. If it is still not clear, shoot me an email. I will be happy to help.

/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare

myString Varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example

Type v_type is Table of Varchar2(300) index by Binary_Integer;

v v_type;


Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',')
Return v_type



is

v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);



Begin

t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
i_s :=1;
tempString:=substr(t_items,i_s);
i:=1;

while i_ep > 0
Loop

i_ep:=instr(tempString,p_delimiter,i_s);


if i_ep >0 then
if i_ep-i_s = 0 then
v_varTab_i(i):= NULL;
i_ep:=i_ep+length(p_delimiter);
else
v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
dbms_output.put_line(v_varTab_i(i));

end if;
i:=i+1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;

End Loop;

Return v_varTab_i;

Exception
when others then
NULL;
Return v_varTab_i;

End GetStrings;
------

Begin

v:=GetStrings(myString,':');

for j in 1..v.LAST Loop

dbms_output.put_line(v(j));

End Loop;

End;

/*-----------------------------------------------------------<---------*/