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

Question

I have a SQL Server table Userswith a column codenamewith a lot of records, for example:

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

I need to make a request for a search form that ignores everything codenamecontaining[LP]

I wrote and ran the following query:

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

This query returns nothing.

I want to get (in this example) entries:

Luis
PedroLuis
JoseLuis

If I ask:

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

I get:

[LP]Luis
JoseLuis
Luis
PedroLuis

and if I add to the request:

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

I get nothing.

Answer 1

All lines have either Lor P, which is what they are looking for %[LP]%.

One way is to avoid the pattern:

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