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.

Answers


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.


Need Your Help

CURL wont load data from https

php curl

My xampp wont load anything on this, I tried it on gearhost, same thing, but on one other free host, it worked.. json was loaded as it should.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.