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;
/*-----------------------------------------------------------<---------*/
4 comments: