Search code examples
.netsqlitedbnull

SQLite equivalent to ISNULL(), NVL(), IFNULL() or COALESCE()

I'd like to avoid having many checks like the following in my code:

myObj.someStringField = rdr.IsDBNull(someOrdinal) 
                            ? string.Empty 
                            : rdr.GetString(someOrdinal);

I figured I could just have my query take care of the nulls by doing something like this:

SELECT myField1, [isnull](myField1, '') 
FROM myTable1
WHERE myField1 = someCondition

I'm using SQLite though and it doesn't seem to recognize the isnull function. I've also tried some equivalent ones recognized in other databases (NVL(), IFNULL() and COALESCE()), but SQLite doesn't seem to recognize any of them.

Does anyone have any suggestions or know of a better way to do this. Unfortunately the database doesn't have default values for all fields. Plus, I need to use some LEFT JOIN clauses in some cases, where some of the fields returned will be null because the matching record in the LEFT JOIN table will not exist.

Solution

  • IFNULL, see here: http://www.sqlite.org/lang_corefunc.html#ifnull

    no brackets around the function