Get all row had value in column

Question

How do I get all rows having the value 1, but not 11 in column news_short?

Here's my table:

id |   news_short  |
 1 | 1,2,3,4,5,6,7 |
 2 | 2,4,5,6,1,5,6 |
 3 | 11,2,5,6,9,4  |

Answer 1

I agree you should normalize. But here is the solution the way it sits.

Select * from table where news_short like '%1,%' and 
news_short not like '%11,%';

Answer 2

FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings

so you should search if a value is != 11

eg:

->where("FIND_IN_SET(news_short) !=", 11)

Answer 3

Your best bet would be normalize your schema do not store relations in form of comma separated list instead create a junction table to maintain a m:m many to many or if its one to many relation between main table and these news_short (probably from other table) values,create a new table as news_short with columns main table id and news(table) id and in each row save one association per your_table and news.

If you aren't able to update/change your schema you could use find_in_set() but its not a good option

select * 
from your_table 
where find_in_set(1, news_short) > 0

demo