COALESCE

<< | FB 2.5 Language Reference | >>
<< | FB 2.1 Language Reference | >>
<< | FB 2.0 Language Reference | >>

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.

See also:
CASE
NULLIF()
DECODE()
IIF()

back to top of page
<< | FB 2.5 Language Reference | >>
<< | FB 2.1 Language Reference | >>
<< | FB 2.0 Language Reference | >>