I want to select rows which has a particular value in the column but the column can contain multiple values

col_1   col_2
0       ab,bc,cd
1       bc,xy
2       zz,xx
3       ab
4       cc
5       ef,kk,ok

I want to select rows that have “ab” as one of the values in col_2. For example – in this case, 0th and 3rd row will be selected. So, is there any SQL query for that?

Answer

First, you should fix your data model. Storing multiple values in a string is just a misuse of strings. The correct data model would have a separate row for each col_1/col_2 combination.

Sometimes, we are stuck with other people’s really bad decisions on data modeling. MySQL actually has a function to help deal with this, find_in_set().

You can use:

where find_in_set('ab', col_2) > 0

until you fix the data model.