SQLite: coalesce Function

This SQLite post explains how to use the SQLite coalesce function with syntax and examples.

Description

The SQLite coalesce function returns the first non-null expression in the list.

Syntax

The syntax for the coalesce function in SQLite is:

coalesce( expression1, expression2, ... expression_n )

Parameters or Arguments

expression1, expression2, ... expression_n

The expressions to test for non-null values. At least 2 expressions must be provided.

Note

• If all expressions evaluate to null, then the coalesce function will return null.
• You must provide at least 2 expressions as parameters when using the coalesce function.

Applies To

The coalesce function can be used in the following versions of SQLite:

• SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x

Example

Let's look at some SQLite coalesce function examples and explore how to use the coalesce function in SQLite.

For example:

sqlite> SELECT coalesce(null, null, null, 'A', 'B');
Output: 'A'

sqlite> SELECT coalesce('A', 'B', null, 'C', 'D');
Output: 'A'

sqlite> SELECT coalesce(null, 1, 2, 3, null, 4);
Output: 1

sqlite> SELECT coalesce(null, 'AODBA.com');
Output: 'AODBA.com'

sqlite> SELECT coalesce(null, 'AODBA.com', 'mysite.com');
Output: 'AODBA.com'

sqlite> SELECT coalesce(null, null, null, null, null);
Output: NULL