CODEWITHSUNDEEP
mysql
Vipul sharma
Vipul sharma

Reputation: 1255

How to match comma seprated string with comma seprated value in mysql?

I want to select rows who have comma separated values with comma separated value in column.

Before i was using FIND_IN_SET(needle,source) method to match single value.

Mysql table is like 

id       data 
1        -a-,-b-,-c-
2        -f-,-g-,-h-
3        -a-,-b-,-h-

I want to get rows which have -c-,-p-,-h- in data column.

Upvotes: 0

Views: 123

Answers (1)

O. Jones
O. Jones

Reputation: 108641

Gordon is correct. Strings of comma-separated values in SQL columns is the number one antipattern in database design. It takes all the optimizations and elegance of SQL and throws it in the trash. Fix it as soon as you can by adding another table.

In the meantime you could try

 SELECT whatever 
   FROM table
  WHERE FIND_IN_SET('-c-', data) > 0
    AND FIND_IN_SET('-p-', data) > 0
    AND FIND_IN_SET('-h-', data) > 0

That finds rows with all three tokens you mention, in any order.

Upvotes: 1

Related Questions