Match comma separated column values

Question

Note: Data is already stored in this format.

I'm trying to match two comma separated values.

Table records:

user_id    state_id      city_id      role_id
  1        1,2,3         1,2          3,4  
  2        5,6,8,10      9,11,15      1,2
  3        7,8           10           2,5

I want to match column values with given values.

Each column should match one or more values.

Tried:

  SELECT user_id
    FROM users 
    WHERE  
    CONCAT(',', state_id, ',') LIKE '%,5,10,%' AND 
    CONCAT(',', city_id, ',') LIKE '%,9,15,%' AND
    CONCAT(',', role_id, ',') LIKE '%,1,%'

Expected output is user id "2" as it is matching given values but above query generating empty result.

Answer 1

You can use find_in_set:

  SELECT user_id
    FROM users 
    WHERE  
    find_in_set('5', state_id) <> 0 AND find_in_set('10', state_id) <> 0 AND 
    find_in_set('9', city_id) <> 0 AND find_in_set('15', city_id) <> 0 AND
    find_in_set('1', role_id) <> 0

Fiddle

But as a general advice, you should normalise your table. Check Is storing a delimited list in a database column really that bad?

Answer 2

This model not is the best, but since you not defined it and you only need to query....

SELECT user_id
  FROM users
  WHERE
  state_id LIKE '%5%10%' AND
  city_id LIKE '%9%15%' AND
  role_id LIKE '%1%'

PS. Take care about your performance, because multiples LIKE in a text field (I'm thinking it's a varchar!) might create other issues.