CODEWITHSUNDEEP
t-sqlsqlitenullisnull
Vector
Vector

Reputation: 11703

Null substitution in SQLite:

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

Upvotes: 4

Views: 4410

Answers (4)

Larry Lustig
Larry Lustig

Reputation: 50970

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).

Upvotes: 3

mu is too short
mu is too short

Reputation: 434685

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.

Upvotes: 7

Bacon Bits
Bacon Bits

Reputation: 32170

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

Upvotes: 2

Giorgi
Giorgi

Reputation: 30883

You can use ifnull:

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

Upvotes: 6

Related Questions