jcon.no: Oracle blog

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;

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

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