6

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
  • 91
  • 1
  • 1
  • 5

2 Answers2

15
Select *
From EMP
Where Cod_Dept = 10 
And Name Not Like '%LA%'
Hüseyin Burak Karadag
  • 1,848
  • 17
  • 23
4

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.

Ilyes
  • 14,640
  • 4
  • 29
  • 55
  • 1
    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. http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex anyway +1 – John Cappelletti Sep 17 '17 at 20:40
  • `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 Sep 17 '17 at 20:47
  • Why `<1` rather than `=0`? – Martin Smith Sep 17 '17 at 20:50
  • @MartinSmith Agreed... with no leading wildcards LIKE is the way to go. That said, my tests of LIKE vs CHARINDEX were not as dramatic as the results in the link, there was a notable differance favoring CHARINDEX – John Cappelletti Sep 17 '17 at 21:21
  • + `Cod_Dept = 10 AND Name NOT LIKE '%LA%'` won't return the rows where `Name IS NULL` @MartinSmith – Ilyes Sep 03 '20 at 06:34