select from comma-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here ;)

create table t(description varchar2(12) primary key, 
  numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION NUMBERS
PRIME 2,3,5,7
ODD 1,3,5,7,9

Now I want to unpivot numbers in rows


select description,(column_value).getnumberval()  
from t,xmltable(numbers)

DESCRIPTION (COLUMN_VALUE).GETNUMBERVAL()
PRIME 2
PRIME 3
PRIME 5
PRIME 7
ODD 1
ODD 3
ODD 5
ODD 7
ODD 9

It is that simple :)

Works also with strings :


select (column_value).getstringval() 
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c

18 thoughts on “select from comma-separated list”

  1. This is an elegant solution in 11g, but how would you do this in Oracle 9i?

    Thanks!

  2. I would create a plsql function

    
    CREATE OR REPLACE TYPE lsc_number AS OBJECT (
       x   NUMBER
    );
    /
    
    CREATE OR REPLACE TYPE lsc_number_collection AS TABLE OF lsc_number;
    /
    
    CREATE OR REPLACE FUNCTION lsc_f (str VARCHAR2, delim VARCHAR2 := ',')
       RETURN lsc_number_collection PIPELINED
    IS
       tmpstr   VARCHAR2 (4000) := str || delim;
    BEGIN
       WHILE tmpstr IS NOT NULL
       LOOP
          PIPE ROW (lsc_number (SUBSTR (tmpstr, 1, INSTR (tmpstr, delim) - 1)));
          tmpstr := SUBSTR (tmpstr, INSTR (tmpstr, delim) + 1);
       END LOOP;
    
       RETURN;
    END;
    /
    
    SQL> select description, x from t, TABLE (lsc_f (numbers));
    
    DESCRIPTION           X
    ------------ ----------
    PRIME                 2
    PRIME                 3
    PRIME                 5
    PRIME                 7
    ODD                   1
    ODD                   3
    ODD                   5
    ODD                   7
    ODD                   9
    

    It should perform better :)

    xmltable is a 10gR2 function, but only in 11g it accepts dynamic programming

  3. Pingback: select from column-separated list | Oracle
  4. sending departments as comma sperated string (10,20) in input parameter (ip_string), could not compile following procedure

    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    open op_emp for
    select E.ENAME,E.EMPNO
    from emp e
    where e.deptno in(
    select (column_value).getstringval()
    from xmltable(ip_string));
    end extract_string;

  5. are you using 11g?

    In 10g you should use :

    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    open op_emp for
    'select E.ENAME,E.EMPNO from emp e
    where e.deptno in( select (column_value).getstringval()
    from xmltable('''||ip_string
      ||'''))';
    end extract_string;
    

  6. but indeed the xmltable(plsqlvariable) does not seem to work in 11g neither. Open a bug by metalink.

    As a workaround you can use xmltable((select ip_string from dual)) instead of xmltable(ip_string)

  7. yes i am using 11g. thanks but the wrokaround also giving error in 11g at compilation

    ERROR line 9, col 15, ending_line 9, ending_col 20, Found ‘select’, Expecting: XMLNAMESPACES -or- string

  8. which 11g are you using?

    In 11.2.0.1.0

    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    open op_emp for
    select E.ENAME,E.EMPNO from emp e
    where e.deptno in( select (column_value).getstringval() from xmltable((select ip_string x from dual)));
    end extract_string;

    Procedure created.

    But I would prefer a no-xml solution like


    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    if regexp_like(ip_string,’^\d+(,\d+)*$’) then
    open op_emp for ‘select E.ENAME,E.EMPNO from emp e where e.deptno in (‘||ip_string||’)’;
    end if;
    end extract_string;
    /

  9. true, but the cost of having dynamic xquery evaluation is tremendous anyway!

    A better alternative would be to use collections instead of strings

    
    create or replace type deptnolist as table of number
    /
    
    create or replace procedure extract_string(ip_string in deptnolist,op_emp out sys_refcursor)
    as
    begin
    open op_emp for select E.ENAME,E.EMPNO from lsc_emp e where e.deptno member of ip_string;
    end extract_string;
    /
    
    var r refcursor;
    set autop on;
    exec extract_string(deptnolist(10,20),:r);
    

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>