SQL Server: Select Where String Doesn't Contain Certain Value
18,090
Solution 1
Select *
From EMP
Where Cod_Dept = 10
And Name Not Like '%LA%'
Solution 2
Here is another option using CHARINDEX()
:
SELECT *
FROM EMP
WHERE Cod_Dept = 10
AND CHARINDEX('LA' , Name , 0) = 0;
In case you have Null
value and you want to return it too:
SELECT *
FROM EMP
WHERE Cod_Dept = 10
AND (CHARINDEX('LA' , Name , 0) = 0) OR (CHARINDEX('LA' , Name , 0) Is Null);
Demo.
Related videos on Youtube
Author by
Jose Mario Jiménez Alfaro
Updated on September 16, 2022Comments
-
Jose Mario Jiménez Alfaro over 1 year
I need to Select the employees from Department10 where the name does not contain the string "LA".
Select * From EMP Where Cod_Dept = 10
-
Jose Mario Jiménez Alfaro over 6 yearsWorked perfect. Thanks!
-
John Cappelletti over 6 yearsSeems to be a never ending debate charindex vs like. I prefer charindex for wildcard searches for two reasons 1) easier to pass a variable without having to concatinate a string with %, and 2) more important ... performance. cc.davelozinski.com/sql/… anyway +1
-
Martin Smith over 6 years
LIKE
is sargable if no leading wildcard so can easily beatCHARINDEX
in that case. Presumably something not tested in that link from a quick glance at the results. WithCHARINDEX
you don't have to worry about escaping characters like%
. Cardinality estimation can be different between the two. -
Martin Smith over 6 yearsWhy
<1
rather than=0
?