Zero replacement in SQLite:

Question

In Sybase and MSSqlServer TransactSQL, we have the IsNull function (columnName, valueForNull) to return the typed value to the default when the column value is null. How can I replicate this functionality in SQLite?

Example in TransactSQL:

select IsNull(MyColumn,-1) as MyColumn from MyTable 

If MyColumn is null, this expression will return -1 as the value of MyColumn. Want to do something like this in SQLite.

(Yes, I know that I can write my own wrapper function to handle this - this is not the answer I'm looking for)

TIA

Answer 1

You can use ifnull :

 select ifnull(MyColumn,-1) as MyColumn from MyTable 

Answer 2

You should use the standard COALESCE function :

 select coalesce(MyColumn, -1) as MyColumn from MyTable 

Any database that understands standard ANSI SQL will support COALESCE, so using it is a good habit.

Answer 3

The standard SQL way to do this is with CASE:

  SELECT CASE WHEN MyColumn IS NULL THEN -1 ELSE MyColumn END FROM MyTable 

Significantly more detailed than the engine-specific functions IFNULL, ISNULL, NZ, but more portable.

(Or, as mu points out, you can use COALESCE much better for this).

Answer 4

SQLite has a built-in IFNULL () function that will do what I think you're trying here.