Home > 11g, sql, xml > select from comma-separated list

select from comma-separated list

July 3rd, 2009
Goto comments Leave a comment

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
Bookmark and Share

  1. Laurent Schneider
    July 3rd, 2009 at 16:13 | #1
    Reply | Quote

    with strings it is more challenging if you want to deal with empty strings, entity escaping, etc…

  2. Dominic Brooks
    July 3rd, 2009 at 16:20 | #2
    Reply | Quote

    Nice

  3. Marco Gralike
    July 3rd, 2009 at 17:12 | #3
    Reply | Quote

    Didn’t realize that “column_value” could be used beyond the scope of xmldb. Thanks.

    M.

  4. Marco Gralike
    July 3rd, 2009 at 17:16 | #4
    Reply | Quote

    Although I knew that you mimic it in 9i via

    select value(Z) AS object_value from MY_TABLE Z;

  5. Joyce Chan
    July 7th, 2009 at 14:59 | #5
    Reply | Quote

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

    Thanks!

  6. Laurent Schneider
    July 7th, 2009 at 16:07 | #6
    Reply | Quote

    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

  7. Tahir
    June 29th, 2010 at 13:33 | #7
    Reply | Quote

    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;

  8. Laurent Schneider
    June 29th, 2010 at 14:40 | #8
    Reply | Quote

    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;

  9. Laurent Schneider
    June 29th, 2010 at 16:03 | #9
    Reply | Quote

    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)

  10. Tahir
    June 30th, 2010 at 08:29 | #10
    Reply | Quote

    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

  11. Laurent Schneider
    June 30th, 2010 at 11:19 | #11
    Reply | Quote

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

  12. Tahir
    June 30th, 2010 at 13:23 | #12
    Reply | Quote

    thanks the error was due to Toad.

    second option is not binding variable.

  13. Laurent Schneider
    June 30th, 2010 at 15:08 | #13
    Reply | Quote

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

  14. Tahir
    June 30th, 2010 at 16:50 | #14
    Reply | Quote

    thanks

  1. July 3rd, 2009 at 17:30 | #1
    Blogroll Report 26/06/2009 – 03/07/2006 « Coskan’s Approach to Oracle
  2. July 10th, 2009 at 19:01 | #2
    Log Buffer #153: a Carnival of the Vanities for DBAs | Pythian Group Blog
  3. August 11th, 2009 at 09:17 | #3
    select from column-separated list | Oracle