CODEWITHSUNDEEP
mysql
dxlliv
dxlliv

Reputation: 482

MySQL check if number is in a string field (with numbers separated by comma)

In my table I've subcategories like

id, name, subcat
1, "john", "4,8,34"
2, "jimmy", "8,32,4"
3, "elthon", "42"

How to do something like SELECT * FROM mytable WHERE 8 IN (subcat)?

Upvotes: 0

Views: 226

Answers (1)

spencer7593
spencer7593

Reputation: 108370

I first want to mention.... storing comma separated lists is a SQL AntiPattern. I recommend Chapter 2 of Bill Karwin's excellent book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming. Avaiable at Amazon and other fine booksellers [https://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557]

To answer the question you asked... with well formed lists, we can use the MySQL FIND_IN_SET function. As a demonstration 

    FIND_IN_SET('8','4,8,34')

evaluates to TRUE.

Reference: FIND_IN_SET http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

If we want to avoid using the MySQL specific FIND_IN_SET function, we could use a LIKE comparison. But to get that to work, we need to add a comma character at the beginning and end of the subcat list, and then search for a comma delimited value, e.g. ',8,' in the list. For example 

 CONCAT(',',subcat,',') LIKE '%,8,%'

Upvotes: 1

Related Questions