How to use LIKE and NOT LIKE together in a SQL Server query
28,937
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 '/';
Author by
Lewis Yuburi
Updated on July 09, 2022Comments
-
Lewis Yuburi almost 2 years
I have this a SQL Server table
Users
with a columncodename
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
codename
s 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.