4

In Sybase and MSSqlServer TransactSQL, we have a function IsNull(columnName,valueForNull) to return a typed default value when a 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 similar in SQLite.

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

TIA

Vector
  • 10,879
  • 12
  • 61
  • 101

4 Answers4

7

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 to get into.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 1
    @Mikey: Using a standard SQL function is a hack? Obscure? I think you are rather mistaken. COALESCE is meant to return the first non-NULL value from a set of *values*, the function doesn't know (nor should it) what a column is as it operates on *values*, not *columns*. The above is the expected and idiomatic use of COALESCE and even matches the example in the [PostgreSQL manual](http://www.postgresql.org/docs/9.0/interactive/functions-conditional.html#AEN15548). Using a non-standard function is a hack, using a standard SQL function as it is intended to be used is not a hack. – mu is too short Nov 03 '11 at 22:29
  • +1 - I retracted my comment - realized that same thing on the way home - not that it's standard - you can hack the use of a standard language feature - but because it examines a set of values, and a hard coded value is just another value that can be a set member. – Vector Nov 04 '11 at 03:20
6

You can use ifnull:

select ifnull(MyColumn,-1) as MyColumn from MyTable
Giorgi
  • 30,270
  • 13
  • 89
  • 125
  • 1
    ifnull(MyColumn,-1) *exactly* answers my question. Still, @Larry Lustig has given us a completely generic SQL solution with no special function at all. – Vector Nov 03 '11 at 20:02
3

The SQL standard way to do this is with CASE:

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

Considerably more verbose than engine-specific IFNULL, ISNULL, NZ functions, but more portable.

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

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
2

SQLite has the IFNULL() built-in function which would do what I think you're trying here.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66