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

select from column-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

  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