SQLite equivalent to ISNULL(), NVL(), IFNULL() or COALESCE()

Question

I'd like to avoid having many checks like the following in my code:

myObj.someStringField = rdr.IsDBNull(someOrdinal) 
                            ? string.Empty 
                            : rdr.GetString(someOrdinal);

I figured I could just have my query take care of the nulls by doing something like this:

SELECT myField1, [isnull](myField1, '') 
FROM myTable1
WHERE myField1 = someCondition

I'm using SQLite though and it doesn't seem to recognize the isnull function. I've also tried some equivalent ones recognized in other databases (NVL(), IFNULL() and COALESCE()), but SQLite doesn't seem to recognize any of them.

Does anyone have any suggestions or know of a better way to do this. Unfortunately the database doesn't have default values for all fields. Plus, I need to use some LEFT JOIN clauses in some cases, where some of the fields returned will be null because the matching record in the LEFT JOIN table will not exist.

Achaean

Answer 1

IFNULL, see here: http://www.sqlite.org/lang_corefunc.html#ifnull

no brackets around the function

Xenocrates

Bah it was the square brackets. Thanks for that. Was driving me crazy that the documentation said it was supported (also has coalesce in there), but it wasn't working. One of those days... – Achaean

I realize your description of "no brackets around the function" references the question, but with a statement like that, it would help to have an example underneath your initial statement. – Thallophyte

Answer 2

Try this

ifnull(X,Y)  

e.g

select ifnull(InfoDetail,'') InfoDetail; -- this will replace null with ''
select ifnull(NULL,'THIS IS NULL');-- More clearly....

The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.

Solent

Yes, I figured out that it was ifnull I wanted... I just needed to drop the square brackets I was using. – Achaean

Answer 3

If there is not ISNULL() method, you can use this expression instead:

CASE WHEN fieldname IS NULL THEN 0 ELSE fieldname END

This works the same as ISNULL(fieldname, 0).

Terina

The function ifnull is the SQLite equivalent of the isnull function the question was asking about. To anyone reading this now, please see SQLMenace's answer (from over a year and a half earlier nevertheless) before writing your own solution using CASE. – Disappearance

@Disappearance - You make a good point. However, this answer is helpful for those potentially using sqlite just for unit testing, and using a different RDBMS for live code. In that case, using something like CASE statements might make more sense than IFNULL. – Plumbo

@sethflowers I can see the value in implementing null default values in a more RDBMS-neutral way but that is the answer to a different question. :) The OP is simply asking for SQLite's equivalent to isnull in other systems and I wouldn't want to encourage people to "roll their own." – Disappearance

I was just looking for something like an ifnotnull() or ifnonnull() to complement ifnull(), for use in string concatenation within the SELECT clause to display "last_name, first_name" but only concatenate the comma if first_name is non-null. This approach let me do that. – Hydrastis

Answer 4

For the equivalent of NVL() and ISNULL() use:

IFNULL(column, altValue)

column : The column you are evaluating.

altValue : The value you want to return if 'column' is null.

Example:

SELECT IFNULL(middle_name, 'N/A') FROM person;

*Note: The COALESCE() function works the same as it does for other databases.

Sources:

• COALESCE() Function (w3schools)
• SQL As Understood By SQLite (SQLite website)

Lyophilize

Answer 5

Use IS NULL or IS NOT NULL in WHERE-clause instead of ISNULL() method:

SELECT myField1
FROM myTable1
WHERE myField1 IS NOT NULL

Affenpinscher

Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this". – Fealty

That's not relevant to what was asked. The OP doesn't want to filter out rows where myField1 has a non-null value, he wants to replace the value in the result column with a different one, if the row value is null. – Fisticuffs

Answer 6

You can easily define such function and use it then:

ifnull <- function(x,y) {
  if(is.na(x)==TRUE) 
    return (y)
  else 
    return (x);
}

or same minified version:

ifnull <- function(x,y) {if(is.na(x)==TRUE) return (y) else return (x);}

Manuelmanuela

That's not SQLite. – Fisticuffs