SQL: NOT LIKE excludes not only values having the matched string but also NULL values

For example, you have a FRUIT table.

Name             Price         Description
-------------     ------------  --------------------------
Mango           2.25            Very good
Banana           1.29            Bad after frozen
Avacado         2.45          


Now, you want to list all the fruits except those have the word "bad" in its description. You use the following sql.

select * from fruit where lower(Description) not like '%bad%';

You expect this:

Name             Price         Description
-------------     ------------  --------------------------
Mango           2.25            Very good
Avacado         2.45        

But you get this:

Name             Price         Description
-------------     ------------  --------------------------
Mango           2.25            Very good

To get the result you want, modify the sql to:

select * from fruit where lower(coalesce(Description, ' ')) not like '%bad%';

If it is a number field, the sql should be:

select * from fruit where lower(coalesce(<field>, 0)) = 0;

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.