FirebirdSQL logo

COALESCE()

Returns the first non-NULL argument

Result type

Depends on input

Syntax
COALESCE (<exp1>, <exp2> [, <expN> ... ])
Table 1. COALESCE Function Parameters
Parameter Description

exp1, exp2 …​ expN

A list of expressions of compatible types

The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument.If all the arguments evaluate to NULL, the result is NULL.

COALESCE Examples

This example picks the Nickname from the Persons table.If it happens to be NULL, it goes on to FirstName.If that too is NULL, “'Mr./Mrs.'” is used.Finally, it adds the family name.All in all, it tries to use the available data to compose a full name that is as informal as possible.This scheme only works if absent nicknames and first names are NULL: if one of them is an empty string, COALESCE will happily return that to the caller.That problem can be fixed by using [fblangref50-scalarfuncs-nullif].

select
  coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
    as FullName
from Persons