sqlmysqldatabasefind-in-set

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?

Solution

  • 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.