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

Question

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.

Answer 1

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

no brackets around the function

Answer 2

Try this

ifnull(X,Y)   

e.g

select ifnull(InfoDetail,'') InfoDetail; -- this will replace null with '' select ifnull(NULL,'THIS IS NULL');-- More clearly.... 

The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.