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

.net sqlite dbnull
Jason Down picture Jason Down · Apr 28, 2009 · Viewed 162k times · Source

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

SQLMenace picture SQLMenace · Apr 28, 2009

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

no brackets around the function

Related questions

What 'additional configuration' is necessary to reference a .NET 2.0 mixed mode assembly in a .NET 4.0 project?

I have a project in which I'd like to use some of the .NET 4.0 features but a core requirement is that I can use the System.Data.SQLite framework which is compiled against 2.X. I see mention of this being …

Read More
Could not load file or assembly 'System.Data.SQLite'

I've installed ELMAH 1.1 .Net 3.5 x64 in my ASP.NET project and now I'm getting this error (whenever I try to see any page): Could not load file or assembly 'System.Data.SQLite, Version=1.0.61.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139…

Read More
Is there a .NET/C# wrapper for SQLite?

I'd sort of like to use SQLite from within C#.Net, but I can't seem to find an appropriate library. Is there one? An official one? Are there other ways to use SQLite than with a wrapper?

Read More