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.

Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Feb 02 '22 at 08:29
I know but data is already stored in this format hence have to work with this only. — glitchy, Feb 02 '22 at 08:31
Does this answer your question? [How can I match a comma separated list against a value?](//stackoverflow.com/q/1142246/https://stackoverflow.com/q/1142246/90527) — outis, Jun 26 '22 at 21:48

Zakaria · Answer 1 · 2022-02-02T08:40:31.397

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?

edited Feb 02 '22 at 08:40

answered Feb 02 '22 at 08:38

Zakaria

4,715
2
5
31

Ok but given values are not fixed they are coming dynamicly. – glitchy Feb 02 '22 at 08:40
1

@glitchy then build the where clause "dynamically". – Salman A Feb 02 '22 at 09:01
@SalmanA Ok. will it impact the performance as it will create long query? – glitchy Feb 02 '22 at 09:06
@glitchy you have a poor database design to begin with. performance should be least of your concerns; get accurate results at least. – Salman A Feb 02 '22 at 09:08

score 0 · Answer 2 · answered Feb 02 '22 at 09:09

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.

Match comma separated column values

2 Answers2