Menu
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.
424
The SQL LIKE and NOT LIKE operators are used to find matches between a string and a given pattern. They are part of standard SQL and work across all database types, making it essential knowledge for all SQL users.
The NOT LIKE operator in SQL is used on a column which is of type varchar . Usually, it is used with % which is used to represent any string value, including the null character \0 . The string we pass on to this operator is not case-sensitive.
SQL not like statement syntax will be like below. SELECT column FROM table_name WHERE column NOT LIKE pattern; UPDATE table_name SET column=value WHERE column NOT LIKE pattern; DELETE FROM table_name WHERE column NOT LIKE pattern; As an example, let's say we want the list of customer names that don't start with 'A'.
So, here is the easiest solution. select * from table1 where column1 not like '%value1%' and column1 not like '%value2%' and column1 not like'%value3%'; If you want to play around with the Boolean logic, you rearrange the query like this.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
