Split data in Oracle

#first create type data.

CREATE OR REPLACE

type SPLIT_TBL as table of varchar2(32767);

/

# and Then create function

CREATE OR REPLACE function split

(

p_list varchar2,

p_del varchar2 := ',' -- default delimeter is coma

) return split_tbl pipelined

is

l_idx pls_integer;

l_list varchar2(32767) := p_list;

l_value varchar2(32767);

begin

loop

l_idx := instr(l_list,p_del);

if l_idx > 0 then

pipe row(substr(l_list,1,l_idx-1));

l_list := substr(l_list,l_idx+length(p_del));

else

pipe row(l_list);

exit;

end if;

end loop;

return;

end split;

/

#how to use???

select COLUMN_VALUE from table(split('DATA-INDRA-VAS','-'));

#Result

COLUMN_VALUE

-------------------

DATA

INDRA

VAS