0

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.

Vipul sharma
  • 1,245
  • 1
  • 13
  • 33
  • 4
    Fix your data model!!! Don't store multiple values in a delimited string. It is not the SQL way to store multiple values. Use a junction/association table. – Gordon Linoff Feb 01 '19 at 11:50
  • Possible duplicate of [MySQL find\_in\_set with multiple search string](https://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string) – Nico Haase Feb 01 '19 at 11:50
  • use `LIKE '%-c-%' ` https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html – Roy Bogado Feb 01 '19 at 11:51

1 Answers1

1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172