SQL Select where values IN List string

Question

I select data with this query

SELECT * FROM `tbl_post` WHERE cate IN (15)

But only records can be found that The number 15 is the first like : "15,16,18" , "15" can not be found If the number is 15 in the middle or last like : "14,15,16"

enter image description here

Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — user3783243, Apr 03 '20 at 17:24
You're having this problem because your database is poorly designed. You'll probably have more problems in the future because of this design mistake of storing multiple values in a column. — Honeyboy Wilson, Apr 03 '20 at 17:39
Never ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Apr 03 '20 at 19:18

score 1 · Accepted Answer · answered Apr 03 '20 at 17:21

MySQL has a specific string function called find_in_set() for the purpose of searching for a value in a comma-separated string:

select * from tbl_post where find_in_set('15', cat) > 0

While this will work for your specific use-case, let me pinpoint that storing delimited list in relational databases is not a good practice, and should be generally avoided. You should have a separate table to store cates, which each value on a separate row.

Recommended reading: Is storing a delimited list in a database column really that bad?

score 0 · Answer 2 · answered Apr 03 '20 at 18:50

Simply using "like" might help -

select * from tbl_post where cate like '15,%' or cate like '%,15,%' or cate 
like '%15';

cate like '15,%' : Will get the 15 placed at the beginning

cate like '%,15,%' : Will get the 15 placed in the middle

cate like '%15' : Will get the 15 placed at the end

SQL Select where values IN List string

2 Answers2