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”

  5. Pingback: Blogroll Report 26/06/2009 – 03/07/2006 « Coskan’s Approach to Oracle

  6. Joyce Chan

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

    Thanks!

  7. Laurent Schneider Post author

    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

  8. Pingback: Log Buffer #153: a Carnival of the Vanities for DBAs | Pythian Group Blog

  9. Pingback: select from column-separated list | Oracle

  10. Tahir

    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;

  11. Laurent Schneider

    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;

  12. Laurent Schneider Post author

    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)

  13. Tahir

    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

  14. Laurent Schneider Post author

    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;
    /

  16. Laurent Schneider Post author

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

  18. Pingback: jcon.no: Oracle Blog - Select on comma-separated list

Comments are closed.