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.

Share:
18,090

Related videos on Youtube

Jose Mario Jiménez Alfaro
Author by

Jose Mario Jiménez Alfaro

Updated on September 16, 2022

Comments

  • Jose Mario Jiménez Alfaro
    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
    Jose Mario Jiménez Alfaro over 6 years
    Worked perfect. Thanks!
  • John Cappelletti
    John Cappelletti over 6 years
    Seems 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
    Martin Smith over 6 years
    LIKE is sargable if no leading wildcard so can easily beat CHARINDEX in that case. Presumably something not tested in that link from a quick glance at the results. With CHARINDEX you don't have to worry about escaping characters like %. Cardinality estimation can be different between the two.
  • Martin Smith
    Martin Smith over 6 years
    Why <1 rather than =0?