Issue with NOT LIKE

Question

I have a table as follows:

ItemID
BrandID

with data as follows (I actually join to another table to get the Brand text but for brevity I have put them into the same table here

ItemID      Brand
   1        Test
   1        Test2
   1        stuff
   2        test
   3        guy
   4        girl
   5        rest

I do a contains search for the term 'es' as follows:

SELECT DISTINCT(ItemID) FROM Items WHERE Brand LIKE '%es%'

which returns Items (1, 2, 5).

I now want to do a does not contain which should return Items (3, 4) however the query

SELECT DISTINCT(ItemID) FROM Items WHERE Brand NOT LIKE '%es%'

also returns Item 1 as the third item 1 does not contain 'es'

so my question:

How can I do a Not Contains which makes sure that it only return Items where none of the records contain the given string?

I hope I have made myself clear on this.

I am using SqlServer Ce 4.0

score 1 · Answer 1 · answered Sep 26 '14 at 09:05

1

SELECT DISTINCT(ItemID) FROM Items WHERE Brand NOT LIKE '%es%'
EXCEPT 
SELECT DISTINCT(ItemID) FROM Items WHERE Brand LIKE '%es%'

answered Sep 26 '14 at 09:05

Jayadevan

1,306
2
12
33

1

This also works SELECT ItemID FROM Items WHERE ItemID NOT IN (SELECT ItemID FROM Items WHERE Brand LIKE '%es%'); – Jayadevan Sep 26 '14 at 09:07

score 1 · Accepted Answer · answered Sep 26 '14 at 09:05

1

SELECT DISTINCT(ItemID)
FROM   Items 
WHERE  ItemID NOT IN 
       (
           SELECT ItemID
           FROM   Items 
           WHERE Brand LIKE '%es%' 
       )

answered Sep 26 '14 at 09:05

Giannis Paraskevopoulos

18,261
1
49
69

This works well, but it takes an age to complete, I shall accept your answer and then experiment with indexes to try to improve the performance – Steven Wood Sep 26 '14 at 09:13

Issue with NOT LIKE

2 Answers2