How to apply filter on comma separated values in oracle?
When I run the below query I get 3 rows instead of 4,
SELECT * FROM freelook WHERE done_type IN ('14','27','28')
Because in 1 row the value is like this 1,14,27.This is the comma separated single value in one column.Like this I have many rows which have comma separated value for done_type.
I want it should separate the values and then check if it matches the given values.
My query is how to apply filter on such row where value is string which is comma separated value. Please guide.
To search for, say, 14 you could:
... WHERE done_type = '14' OR done_type LIKE '14,%' OR done_type LIKE '%,14' OR done_type LIKE '%,14,%'
Rinse and repeat for 27, 28 and any other number you want to search for...
This will, however, perform horribly (full table scan).
Instead of putting paint on rust, better to cure the underlying problem1 and normalize the data by splitting to two tables.
1 Of violating the principle of atomicity and therefore the 1NF.