SQL LIKE operator not working for comma-separated

问题:

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?

回答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?

回答2:

You could use:

WHERE ','+col+',' LIKE '%,8,%'

And the obligatory admonishment: avoid storing lists, bad bad, etc.

回答3:

How about:

where
    col like '8,%'
    or col like '%,8,%'
    or col like '%,8'
    or col = '8'

But ideally, as bluefeet suggests, normalizing this data instead of storing as delimited text will save you all kinds of headaches.