select from a comma-separated string

This is one question I solved today with XML on the developpez.net/forums

I have a table T

I want to select un,deux,trois,quatre,cinq,six,sept,huit.

I could well have written a plsql function like

create or replace type c as object (value varchar2(40));
/
create or replace type t_c as table of c;
/
sho err
create or replace function f(
list varchar2,
sep varchar2 default ',')
return t_c pipelined is
i number:=1;
begin
loop
if (instr(list,sep,1,i)>0)
then
if (i=1) then
pipe row(c(substr(list,
1, instr(list,sep)-1)));
else
pipe row(c(substr(list,
instr(list,sep,1,i-1)+1,
instr(list,sep,1,i)-
instr(list,sep,1,i-1)-1)));
end if;
else
if (i=1) then
pipe row(c(list));
else
pipe row(c(substr(list,
instr(list,sep,1,i-1)+1)));
end if;
return;
end if;
i:=i+1;
end loop;
end;
/
select distinct value
from t,
table(f(c))
;


or anything using recursion or whatever.

I just tried with XML

SQL> select distinct extractvalue(column_value,’/x’)
2 from t,
3 table(xmlsequence(extract(xmltype(
4 ‘<list><x>’||replace(
5 c,’,’,'</x><x>’)||
6 ‘</x></list>’),
7 ‘/list/x’)));

8 rows selected.