1

I have user tables which contains column city_id and inside this column city ids are stored as comma separated values as following:

user_id  user_name     city_id    
1        abc1           1,2,3
2        abc2           15,2,9
3        abc5           1,2,13

Now I want to search rows which contains city id = 1,9,13 from city_id column.

How it can be done?

Expected Ouput:

abc, abc1, abc3
Ankur Mishra
  • 1,284
  • 1
  • 6
  • 15
  • 2
    Don't store the data like that. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – user3783243 May 25 '19 at 15:39

1 Answers1

2

You should avoid storing CSV data in the city_id column as you are currently doing. Ideally, one record should have just one data point for city_id. That being said, MySQL offers a function FIND_IN_SET which can cope with CSV data, at least to a point. Try the following query:

SELECT user_name
FROM yourTable
WHERE
    FIND_IN_SET('1', city_id) > 0 OR
    FIND_IN_SET('9', city_id) > 0 OR
    FIND_IN_SET('13', city_id) > 0;

We can make the query even tighter by using REGEXP with the regex alternation \b(1|9|13)\b:

SELECT user_name
FROM yourTable
WHERE city_id REGEXP '[[:<:]](1|9|13)[[:>:]]';
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • (As a note, `[[:<:]]` is the leading POSIX equivalent of `\b` and `[[:>:]]` is the closing. MySQL uses POSIX, not PCRE, so regexs need to be modified accordingly.) – user3783243 May 25 '19 at 16:00