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.

Published by

Laurent Schneider

Oracle Certified Master

3 thoughts on “select from a comma-separated string”

  1. 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.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>