Kareem Badr <[EMAIL PROTECTED]> wrote: > I read the section on the SQLite website regarding the inconsistencies > of how NULL values are handled in various databases. I didn't see > anything that mentioned what I am running into, though. > > In a nutshell, the following queries do not return rows when test_field > is NULL. It seems counter-intuitive to me. > > SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' > SELECT * FROM my_table WHERE test_field != 'test value' > > I can understand the NOT LIKE query not returning rows with NULL more > than I can understand the != query not returning any rows. NULL does not > equal 'test value'. > > Is there an easy way around this, other than adding "OR test_field IS > NULL" to my queries? The example above is a lot simpler than the case I > need to handle in production, so I would like to avoid having to modify > every query to handle NULL values in a special case. >
I usually use the coalesce() function to fix this. For example: SELECT * FROm table WHERE coalesce(test_field,'') != 'test value'; The "coalesce(test_field,'')" will convert all NULL values of test_field to an empty string and leave all other values unchanged. -- D. Richard Hipp <[EMAIL PROTECTED]>
