SQLite: coalesce Function

This SQLite tutorial 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');
Result: 'A'

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

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

sqlite> SELECT coalesce(null, 'techonthenet.com');
Result: 'techonthenet.com'

sqlite> SELECT coalesce(null, 'techonthenet.com', 'bigactivities.com');
Result: 'techonthenet.com'

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