Query to obtain value among selected values in a comma separated varchar

I have an SQL table as given below

  title                     genre
--------------------|----------------------         
The Dark Knight     |  Action, Crime, Drama
The Godfather       |  Crime,Drama
Inception           |  Sci-Fi

How can I write a Select query such that all titles with genre “Crime” will be displayed

Answer

First, you should fix your data model! Storing multiple values in a column is a really, really, really bad idea.

That said, sometimes we are stuck with other people’s really, really, really bad decisions. MySQL has a convenient function find_in_set() for this purpose. But you need comma-separate values with no spaces. So:

select t.*
from t
where find_in_set('Crime', replace(genres, ' ', '')) > 0;