Select on comma-separated list

I’ve been busy working on a Oracle 11g upgrade. When working on different issues you always stumble over some interesting blogs. This time I found an article about how to select on a comma-separated list. This is worth making a note of. I have not tested the performance of this yet, but will do so in a short while (I hope :-)).

This is the test table Laurent used:

create table t(description varchar2(12) primary key, 
  numbers varchar2(4000));
insert into t(description, numbers) values ('EVEN','2,4,6,8,10');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;
Description Numbers
EVEN 2,4,6,8,10
ODD 1,3,5,7,9

Then you could query the list by using something like:

select * from (
select description,(column_value).getnumberval() number  
from t,xmltable(numbers)) o
where o.number >= 5 and o.description='ODD';
Description Numbers
ODD 5
ODD 7
ODD 9

Laurent also shows that this techique can be used on a list of characters.

2 Responses to “Select on comma-separated list”

  1. 1
    Laurent Schneider Says:

    You will note that 4,6,8 and 10 are not “prime numbers” but “even numbers” :)

    the technic is usually slow, you do not need to test this …

  2. 2
    Lasse Jenssen Says:

    Hi Laurent. Uppsss … Thanks for your comment. I didn’t really pay too much attention to the meaning of the numbers. But you are definitetly right!

    I once used the XMLAGG function going the other way; concatinating text from several rows into one text field (one row). I used combination of XMLAGG and XMLELEMENT. My experience was that these XML-function was also slow. That’s why I wanted to check the performance using the syntax. But if you say it’s usally slow, it confirms my asumption.

Leave a Reply