Shanka Shanka - 1 year ago 58
SQL Question

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.

Please help.

Answer Source

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 consist them, than NULL NOT LIKE '%TAX%' will return false and therefore won't be selected.

I advise you to read about handeling 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