CODEWITHSUNDEEP
sqlsqlite
Thomas Tempelmann
Thomas Tempelmann

Reputation: 12079

Is there a way to make NULL behave like 0 (zero) or like an empty string in SQL?

Assume a sqlite database with an integer column.

Now, it tends to happen that the integer field contains NULL values (=unset) as well.

I would like to interpret NULL values as zero (0) when doing queries on that field.

Is there a way to tell sqlite that I like NULL handled like 0 in this case, especially when I do comparisons using a SELECT statement?

Since I construct the queries dynamically in C-like code, I like to be able to write something like this:

query = "SELECT * FROM tbl WHERE intField=" + IntToStr(anIntValue)

Currently, I work around this with code as follow, which I like to avoid:

if (anIntValue == 0) {
  query = "SELECT * FROM tbl WHERE intField IS NULL OR intField=0"
} else {
  query = "SELECT * FROM tbl WHERE intField=" + IntToStr(anIntValue)
}

Maybe there's an operator I can use in the query that converts NULLs to other values I specify?

Of course, another way would be to make sure one never ends up with NULL values in that field, but one might want to be able to tell when the value hasn't been set at all yet, so I like to be able to keep the NULL values in the database.

Upvotes: 3

Views: 4121

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726669

Although you could use null coalesce functionality of your RDBMS, a universal approach exists that lets you treat NULLs as zeros in a condition, like this:

String val = IntToStr(anIntValue)
"SELECT * FROM tbl WHERE intField=" + val + " OR (0="+ val+" AND intField IS NULL)"

Upvotes: 1

dnoeth
dnoeth

Reputation: 60472

In Standard SQL this is called COALESCE:

COALESCE(col, 0)

Why using a proprietary extension like IFNULL/NVL if there's a Standard which is supported by every DBMS?

Upvotes: 7

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can use IFNULL or

CASE WHEN fieldname IS NULL THEN 0 ELSE fieldname END

This works the same as isnull(fieldname, 0)

You can read more here

Upvotes: 2

user3278460
user3278460

Reputation:

please, try ifnull function, see doc at http://www.sqlite.org/lang_corefunc.html#ifnull

Upvotes: 3

Related Questions