1

I have the follwing simple MySQL query that returns 0 results:

SELECT d.name FROM document d WHERE 10 IN (d.categories)

"categories" is of type varchar and contains for example "10,20,30".

When I type the IN values directly it works and returns a result:

SELECT d.name FROM document d WHERE 10 IN (10,20,30)

I suspect MySQL substitutes d.documents with something like this which of course is not what I want:

SELECT d.name FROM document d WHERE 10 IN ("10,20,30")

What is a proper workaround for this?

Alex
  • 53
  • 5

2 Answers2

6

When you are providing the value as "10,20,30" then it is treated as a single value as against your expected three distinct values. To make the value as three distinct values you need to use the find_in_set function in MySQL.

Also I would suggest you to go through this thread: Is storing a delimited list in a database column really that bad?

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
2

Yes with find_in_set :

SELECT d.name FROM document d WHERE find_in_set(10, d.categories) > 0
Daniel E.
  • 2,440
  • 1
  • 14
  • 24