sqlite query with IN parameter in different condition

Question

I have a table in sqlite named Devices which has following columns

Id, DeviceId, FavBrands, FavCategories

the sample data in it is

 1        50ea9017cb5ad            1,2,8,9           4,6,8
 2        50ead1683dcc0            3,7,9             4,5,9
 3        50ee73b56c4ea            1,2,7             6,7,8

I want to query this table to return deviceid which has FavBrands 7 in them relationg to table above it should give row number 2 and 3.

Thanks

score 2 · Answer 1 · edited May 23 '17 at 12:21

2

You may use this:

SELECT * FROM Devices
WHERE instr(','||FavBrands||',' , ',7,')>0;

(An obligatory remark about please normalizing your database, with a link to Is storing a delimited list in a database column really that bad?).

If you heed another advice and use LIKE, ensure you use '%,7,%' and add commas as we do here, to avoid false positives (e.g. with 77 in the list).

edited May 23 '17 at 12:21

Community

1
1

answered Jan 21 '13 at 13:20

Anton Kovalenko

20,999
2
37
69

but what if there is no comma after 7 just like in row 3.... it s ',7' so will it match – 1Mayur Jan 22 '13 at 05:59
instr is not supported in sqlite – 1Mayur Jan 22 '13 at 11:15
old version, maybe? http://www.sqlite.org/lang_corefunc.html (as of "7" at the end, that's why I *both* append *and* prepend comma to FavBrands) – Anton Kovalenko Jan 22 '13 at 11:19

sqlite query with IN parameter in different condition

1 Answers1