How to use LIKE and NOT LIKE together in a SQL Server query

Question

I have this a SQL Server table Users with a column codename with a lot of records, example:

...
[LP]Luis
JoseLuis
[LP]Pedroso
Luis
PedroLuis
[LP]Maria
CarlosJose
MariaJose
[LP]Carlos
Pedro
...

I need to make a query for a search form that ignore all codenames that contain [LP]

I wrote and run the following query:

SELECT TOP (15)* 
FROM [Users] 
WHERE [codename] LIKE '%Luis%'
AND [codename] NOT LIKE '%[LP]%'

This query doesn't return anything.

I want to get (In this example) the records:

Luis
PedroLuis
JoseLuis

If I query:

SELECT TOP (15) * 
FROM [Users] 
WHERE [codename] LIKE '%Luis%'

I get:

[LP]Luis
JoseLuis
Luis
PedroLuis

and if I add to the query:

AND [codename] NOT LIKE '%[LP]%'

I get nothing.

score 20 · Accepted Answer · edited Mar 02 '16 at 07:45

20

All of the strings have either an L or P, which is what %[LP]% looks for.

One way is to escape the pattern:

SELECT TOP (15) * 
FROM [Users] 
WHERE [codename] LIKE '%Luis%' AND
      [codename] NOT LIKE '%/[LP/]%' escape '/';

edited Mar 02 '16 at 07:45

chancrovsky

answered Mar 02 '16 at 02:55

Gordon Linoff

3

Upvoted. You could also escape `[` by enclosing it in `[]`, i.e. `codename NOT LIKE '%[[]LP]%'`. – Felix Pamittan Mar 02 '16 at 02:56
Ok, I did not know that [] is used as wildcards, that was my mistake.. Both solutions work correctly. I'll use the @Felix-Pamittan's solution. Thanks! – Lewis Yuburi Mar 02 '16 at 03:05
@Gordon Linoff, When we use starting % wildcard in like query at that time index for that column will be used or not? If not how to overcome that issue? – RGS Mar 02 '16 at 04:49
1

@RGS . . . A standard index on a string column is used for `LIKE` only when the pattern does not begin with a wildcard. – Gordon Linoff Mar 03 '16 at 03:56

1 Answers1