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.