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?

What is it that you expect `column not like '%_8%'` to do? Because `'6,8,9'` *is* `like '%_8%'`. (Actually, the strange thing is that you *do* get `'8,9,18'`, since it's *also* `like '%_8%'`.) — ruakh, Jul 09 '13 at 22:04
Why are you storing data as a comma-separated list? If you properly normalized your data you would not have this problem. — Taryn, Jul 09 '13 at 22:04
Note that `_` is a wildcard for _any single character_. This might be the confusing part? — sehe, Jul 09 '13 at 22:06
The `_` implies that there must be a character there @ruakh, the reason why the OP gets what they do is because they all start with 8 and therefore the 8 doesn't have a leading character (I think :-). — Ben, Jul 09 '13 at 22:07
@Ben: Sure, `'8,9,18'` has an `8` that's not preceded by another character, but it also has an `8` that *is* preceded by another character. `'8,9,18' LIKE '%_8%'` should be "true", and `'8,9,18' NOT LIKE '%_8%'` should be "false". I find it hard to believe that the leading `8` would trick SQL Server 2008 into not noticing the non-leading `8`; that would be a pretty embarrassing bug. — ruakh, Jul 09 '13 at 22:10
@Ruakh is right. Only one row, the one with `'8'` should be returned with these data. — ypercubeᵀᴹ, Jul 09 '13 at 22:13
[Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) — Bill Karwin, Jul 09 '13 at 22:21
SSRS sends in values as a comma delimited string when you allow multiple values for a parameter. Advanced. But it doesn't really mean you are storing data in this fashion. — TamusJRoyce, Oct 08 '15 at 16:59

score 11 · Accepted Answer · edited May 23 '17 at 11:45

11

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?

edited May 23 '17 at 11:45

Community

1
1

answered Jul 09 '13 at 22:08

Ron.B.I

2,726
1
20
27

1

Instead of that `WHERE column LIKE '%,8,%' OR column LIKE '%,8' OR column LIKE '8,%'` you could have simply put `WHERE ',' + column + ',' LIKE '%,8,%'` - just personal preference probably... EDIT: nevermind, looks like someone else already answered this :) – T_D Apr 21 '15 at 09:11

score 8 · Answer 2 · answered Jul 09 '13 at 22:08

8

You could use:

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

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

answered Jul 09 '13 at 22:08

Hart CO

34,064
6
48
63

They probably didn't like that you told them not to break the relational model with a relational database. – swasheck Jul 09 '13 at 22:21

score 3 · Answer 3 · answered Jul 09 '13 at 22:07

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.

answered Jul 09 '13 at 22:07

Joe Enos

39,478
11
80
136

SQL LIKE operator not working for comma-separated lists

3 Answers3

Linked