SQL IN Clause only returning rows with first match in comma separated list of IDs

Question

I have 5 users which have a column 'shop_access' (which is a list of shop IDs eg: 1,2,3,4)

I am trying to get all users from the DB which have a shop ID (eg. 2) in their shop_access

Current Query:
SELECT * FROM users WHERE '2' IN (shop_access)

BUT, it only returns users which have shop_access starting with the number 2.

E.g

User 1 manages shops 1,2,3
User 2 manages shops 2,4,5
User 3 manages shops 1,3,4
User 4 manages shops 2,3

The only one which will be returned when running the IN Clause is User 2 and User 4.

User 1 is ignored (which it shouldn't as it has number 2 in the list) as it does not start with the number 2.

I'm not in a position to currently go back and change the way this is set up, eg convert it to JSON and handle it with PHP first, so if someone can try to make this work without having to change the column data (shop_access) that would be ideal.

Please tag your question with the database you are running: mysql, oracle, postgresql...? — GMB, Sep 14 '20 at 08:02
Can you change the query to do a join on users and the shop access table WHERE shopid = 2? — Captain Kenpachi, Sep 14 '20 at 08:03

GMB · Accepted Answer · 2020-09-14T08:07:55.393

2

A portable solution is to use like:

where concat(',', shop, ',') like '%,2,%'

Or if the value to search for is given as a parameter:

where concat(',', shop, ',') like concat('%,', ?, ',%')

Depending on your database, there may be neater options available. In MuSQL:

where find_in_set('2', shop)

That said, I would highly recommend fixing your data model. Storing CSV data in a database defeats the purpose of a relational database in many ways. You should have a separate table to store the user/shop relations, which each tuple on a separate row. Recommended reading: Is storing a delimited list in a database column really that bad?.

edited Sep 14 '20 at 08:07

answered Sep 14 '20 at 08:04

GMB

216,147
25
84
135

1

LIKE '%2%' should do the trick in your first example. The percentage sign does an empty string check too. – Captain Kenpachi Sep 14 '20 at 08:07
@CaptainKenpachi '%2%' will not do the trick as it will also match 12,22,242,... which I don't think is intended – Conffusion Sep 14 '20 at 08:16

score 1 · Answer 2 · answered Sep 14 '20 at 08:07

1

Also, you might want to consider using REGEXP here for an option:

SELECT *
FROM users
WHERE shop_access REGEXP '[[:<:]]2[[:>:]]';
                            -- [[:<:]] and [[:>:]] are word boundaries

answered Sep 14 '20 at 08:07

Tim Biegeleisen

502,043
27
286
360

Prem Parmar · Answer 3 · 2020-09-14T09:06:15.603

0

SELECT * FROM users WHERE (shop_access = 2) OR (shop_access LIKE "2,%" OR shop_access LIKE "%,2,%" OR shop_access LIKE "%,2")

edited Sep 14 '20 at 09:06

answered Sep 14 '20 at 09:01

Prem Parmar

1
1

3

Try to add a little description / context / reasoning to your answer, it will be more helpful. – mfit Sep 14 '20 at 09:07

SQL IN Clause only returning rows with first match in comma separated list of IDs

3 Answers3