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.

Answer 1

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 '/';