SQL Not Like Does Not Appear to be Working

Question

What am I doing wrong? I am tearing my hair out here!

I have two tables from different servers, linked by an ID field. basically in one server the a colour is recorded properly (white, black etc.) however in the second the colour is recorded with a description (White - Vic Window, Rose - Vic Open In Door) My task is to produce a query that will return those records where the colours don't match.

I have produced the following:

SELECT a.orderid, 
       a.colour    AS LoggedColor, 
       b.groupdesc AS [Processed Color] 
FROM   [Server].[DB].[dbo].[frames] AS b 
       INNER JOIN tblorder AS a 
               ON a.orderid = b.header_id 
WHERE  b.colour NOT LIKE '%' + a.colour + '%'

However the like statement does not appear to be working as it is returning the following:

Logged Colour = White

Processed Colour = White - Vic Window

a further look shows that it is in fact returning all records.

FYI I cannot change the database structure - I have had many heated discussions with the creator about what I think of it.

Thanks in advance

Are there spaces involved? Can you add this column to your query: `'%' + a.colour + '%' as SOMETHING` and see what it says? — Lasse V. Karlsen, Jan 15 '14 at 10:19
@Lasse V Karlsen - Well sit me down and call me susie! how did I not notice that! I just added a simple RTRIM to the query and it worked - dont i feel like a pillock! - if you write this as an answer I shall accept it. — Steven Wood, Jan 15 '14 at 10:22
Glad to be of help Susie, but @Raj already posted it as an answer so accept his ;) — Lasse V. Karlsen, Jan 15 '14 at 10:26

score 2 · Accepted Answer · answered Jan 15 '14 at 10:23

Looks like you have some spaces. Try this:

SELECT a.orderid, 
       a.colour    AS LoggedColor, 
       @a_colour
       b.groupdesc AS [Processed Color] 
FROM   [Server].[DB].[dbo].[frames] AS b 
       INNER JOIN tblorder AS a 
               ON a.orderid = b.header_id 
WHERE  b.colour NOT LIKE '%' + LTRIM(RTRIM(a.colour)) + '%'

score -1 · Answer 2 · answered Jan 15 '14 at 10:21

-1

The correct syntax is

WHERE NOT b.colour LIKE '%' + a.colour + '%'

answered Jan 15 '14 at 10:21

Alexander

2,457
1
14
17

4

It is correct that that is also usable syntax, but the answer also implies that `A NOT LIKE B` is *not* correct syntax, [which is incorrect](http://technet.microsoft.com/en-us/library/ms179859.aspx). – Lasse V. Karlsen Jan 15 '14 at 10:27
@LasseV.Karlsen... hmm.. and .. where b.color not like '%a.color%' anything wrong with this ? – vhadalgi Jan 15 '14 at 10:41

SQL Not Like Does Not Appear to be Working

2 Answers2