CODEWITHSUNDEEP
sqlsql-servert-sqlsql-like
Shanka
Shanka

Reputation: 821

NOT LIKE and LIKE not returning opposite result

I have a table with 200 records out of which 10 records has text containing the word 'TAX'.

When I'm executing

Select * from tbl1 WHERE [TextCol] LIKE '%TAX%'

then I get the result set with those 10 records correctly .

But when I am trying to exclude those records by

Select * from tbl1 WHERE [TextCol] NOT LIKE '%TAX%'

it's returning 100 records only, instead of 190.

Upvotes: 54

Views: 9006

Answers (4)

Tomas Hesse
Tomas Hesse

Reputation: 395

I had the same problem with the IN operator on simple int column with nulls. I found that these where not each others inverse as I thought. (I could tell by the row count) 

select * from Dest where id in(select id from Source)
select * from Dest where id NOT in(select id from Source)

To get each others invert I had too rewrite them as such:

select * from Dest where isnull(id,-2)  in(select isnull(id,-1) from Source) 
select * from Dest where isnull(id,-2) NOT in(select isnull(id,-1) from Source)

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272386

(A) SQL comparison operators result in three possible values: True, False and Unknown. If one or both operands are NULL then the result is Unknown. Consider the following example where we compare some values (a person's age) with a constant (18):

21   >= 18 -- True
15   >= 18 -- False
NULL >= 18 -- Unknown

As you can see, the database can/will not decide if NULL is greater than/equal to 18.

(B) The database will only return rows where the WHERE clause evaluates to True. Inverting the expression (e.g. WHERE age >= 18 changed to WHERE age < 18) does not affect Unknown results.

You can use the IS [NOT] NULL to match NULL values. The following query will select the rows where the column does not match the pattern OR the column is NULL:

WHERE [TextCol] NOT LIKE '%TAX%' OR [TextCol] IS NULL

Functions such as ISNULL and COALESCE can be used to transform NULL into some value.

Upvotes: 19

Chanukya
Chanukya

Reputation: 5893

  1. Select * from tbl1 
WHERE ([TextCol] NOT LIKE '%TAX%') AND ([TextCol] NOT LIKE '%TAX%')

  2. select * from tbl1
where [TextCol] NOT LIKE '%TAX%' OR [TextCol] IS NULL

Upvotes: 0

sagi
sagi

Reputation: 40491

Does this return the correct result ?

Select * from tbl1 WHERE COALESCE([TextCol],'-1') NOT LIKE '%TAX%'

I believe NULL values are the issue here, if the column contains them, then NULL NOT LIKE '%TAX%' will return UNKNOWN/NULL and therefore won't be selected.

I advise you to read about handling with NULL values , or here.

As @ughai suggested, if performance is an issue you can also use:

  Select * from tbl1 
  WHERE [TextCol] NOT LIKE '%TAX%'
     OR [TextCol] IS NULL

Upvotes: 76

Related Questions