SQL LIKE operator not working for comma-separated lists

Question

Here is my data:

Column:
8
7,8
8,9,18
6,8,9
10,18
27,28

I only want rows that have and 8 in it. When I do:

Select * 
from table 
where column like '%8%'

I get all of the above since they contain an 8. When I do:

Select * 
from table 
where column like '%8%' 
   and column not like '%_8%'

I get:

8
8,9,18

I don't get 6,8,9, but I need to since it has 8 in it.

Can anyone help get the right results?

Answer 1

I would suggest the following :

SELECT *
FROM TABLE
WHERE column LIKE '%,8,%' OR column LIKE '%,8' OR column LIKE '8,%' OR Column='8';

But I must say storing data like this is highly inefficient, indexing won't help here for example, and you should consider altering the way you store your data, unless you have a really good reason to keep it this way.

Edit:

I highly recommend taking a look at @Bill Karwin's Link in the question's comment:

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