meta data for this page
  •  

COALESCE()

Available in: DSQL, PSQL

Added in: 1.5

Description

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, NULL is returned.

Result type: Depends on input.

Syntax 

COALESCE (<exp1>, <exp2> [, <expN> ... ])

Example 

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

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. Notice that this scheme only works if absent nicknames and first names are really NULL: if one of them is an empty string instead, COALESCE will happily return that to the caller.

Note: In Firebird 1.0.x, where COALESCE is not available, you can accomplish the same with the *nvl external functions.