The coalesce function returns the first of the parameters that has a valid non-NULL representation. Any number of parameters can be used.
Syntax:
coalesce(expr1[ , expr2 , expr3 , ...])
Arguments:
| Argument | Description |
|---|---|
| expr1 | The first expression to check for a valid non-NULL representation. |
| expr2 | The second expression to check for a valid non-NULL representation. |
| expr3 | The third expression to check for a valid non-NULL representation. |
Examples:
| Example | Result |
|---|---|
| This expression changes all the NULL values of a field to 'N/A'. | |
|
Coalesce(ProductDescription, ProductName, ProductCode, 'no description available') |
This expression will select between three different product description fields, for when some fields may not have values for the product. The first of the fields, in the order given, with a non-null value will be returned. If none of the fields contain a value, the result will be 'no description available'. |
|
Coalesce(TextBetween(FileName, '"', '"'), FileName) |
This expression will trim potential enclosing quotes from the field FileName. If the FileName given is quoted, these are removed, and the enclosed, unquoted FileName is returned. If the TextBetween function doesn't find the delimiters it returns null, which the Coalesce rejects, returning instead the raw FileName. |