Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 329
Vector Avatar asked Dec 03 '22 00:12

Vector


2 Answers

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.

like image 91
mu is too short Avatar answered Dec 28 '22 14:12

mu is too short


You can use ifnull:

select ifnull(MyColumn,-1) as MyColumn from MyTable
like image 40
Giorgi Avatar answered Dec 28 '22 15:12

Giorgi