COALESCE

In This Topic

COALESCE( expression1, expression2 [,...n] )

Returns the first non null expression in the argument list.

If all arguments are NULL COALESCE will return NULL.

Coalesce may be used in select statements as a subquery to perform a sort of NULL replacement ability.

SELECT COL1, COL2, COALESCE(COL3, 0) FROM SIMPLETABLE;

In this case if the COL3 contained a null it would be replaced with 0 in the returned recordset.

Returns 1 because it is the first non null expression.

Returns 2 because it is the first non null expression.

select COALESCE( null, 2, 3, 4, 5 );

select COALESCE( 1, 2, 3, 4, 5, null );