Search string using where ,Like not working

I need to fetch 'Apple'from the Description column of a table where user search 'The apple not the'. I have written something like this

set @ExactKey = N'The Apple'
set @NotKey =N'the'


SELECT R.ResourceId
FROM开发者_运维百科 Resource R
WHERE Description LIKE @ExactKey  OR ExternalIdentifier LIKE @ExactKey 
AND ExternalIdentifier NOT LIKE @NotKey OR  Description NOT LIKE  @NotKey 

This is not working

To use a LIKE, you need to include %.

Change your search strings to these:

set @ExactKey = N'%The Apple%' 
set @NotKey = N'%the%' 

However this isn't going to work, unless you have case sensitivity turned on, as the will match the in the apple.

You'd need to force a binary or case sensitive collation for the LIKE and use leading and trailing % wildcards (Edit: as noted by ck).

I'm also guessing your ANDs/ORs are wrong too because of:

• operator precedence
• I assume you want to have the LIKE/NOT LIKE on the same column

Something like

WHERE
    (
       Description COLLATE Latin1_General_BIN LIKE @ExactKey
       AND
       Description COLLATE Latin1_General_BIN NOT LIKE @NotKey
    )
    OR
    (
       ExternalIdentifier COLLATE Latin1_General_BIN LIKE @ExactKey
       AND
       ExternalIdentifier COLLATE Latin1_General_BIN NOT LIKE @NotKey
    )

You'd be better with full text search though. What about the words "theory" or "tithe"?