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

Un,Trois,Cinq,Six
Un,Deux,Quatre
Trois
Sept,Huit
Un,Six

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’)));

EXTRACTVALUE(COLUMN_VALUE,’/X’)
——————————-
Trois
Un
Quatre
Cinq
Six
Deux
Sept
Huit

8 rows selected.

3 Comments

  • I worked it out:

    SQL> select distinct value
    2 from t,
    3 table(f(col2))
    4 ;

    VALUE
    ——————————
    teapot
    candelabra
    silver
    gilt
    antique
    earthenware
    georgian

    8 rows selected.

    I put “col2” into the wrong place in your code not realising the importance of the “value” keyword. My bad.

Leave a Reply

Your email address will not be published.