<< User-Defined Functions (UDFs) | Firebird Null Guide | Altering populated tables >>

Converting to and from NULL

Substituting NULL with a value

The COALESCE function

The COALESCE function in Firebird 1.5 and higher can convert NULL to most anything else. This enables you to perform an on-the-fly conversion and use the result in your further processing, without the need for if (MyExpression is null) then or similar constructions. The function signature is:

 COALESCE( Expr1, Expr2, Expr3, ... )

COALESCE returns the value of the first non-NULL expression in the argument list. If all the expressions are NULL, it returns NULL.

This is how you would use COALESCE to construct a person's full name from the first, middle and last names, assuming that some middle name fields may be NULL:

 select FirstName
        || coalesce( ' ' || MiddleName, '' )
        || ' ' || LastName
 from Persons

Or, to create an as-informal-as-possible name from a table that also includes nicknames, and assuming that both nickname and first name may be NULL:

 select coalesce ( Nickname, FirstName, 'Mr./Mrs.' )
        || ' ' || LastName
 from OtherPersons

COALESCE will only help you out in situations where NULL can be treated in the same way as some allowed value for the datatype. If NULL needs special handling, different from any other value, your only option is to use an IF or CASE construct after all.

Firebird 1.0: the *NVL functions

Firebird 1.0 doesn't have COALESCE. However, you can use four UDFs that provide a good part of its functionality. These UDFs reside in the fbudf lib and they are:

• iNVL, for integer arguments
• i64NVL, for bigint arguments
• dNVL, for double precision arguments
• sNVL, for strings

The *NVL functions take exactly two arguments. Like COALESCE, they return the first argument if it's not NULL; otherwise, they return the second. Please note that the Firebird 1.0 fbudf lib – and therefore, the *NVL function set – is only available for Windows.

Converting values to NULL

Sometimes you want certain values to show up as NULL in the output (or intermediate output). This doesn't happen often, but it may for instance be useful if you want to exclude certain values from summing or averaging. The NULLIF functions can do this for you, though only for one value at the time.

Firebird 1.5 and up: the NULLIF function

The NULLIF internal function takes two arguments. If their values are equal, the function returns NULL. Otherwise, it returns the value of the first argument.

A typical use is e.g.

 select avg( nullif( Weight, -1 ) ) from FatPeople

which will give you the average weight of the FatPeople population, without counting those with weight -1. (Remember that aggregate functions like AVG exclude all NULL fields from the computation.)

Elaborating on this example, suppose that until now you have used the value -1 to indicate "weight unknown" because you weren't comfortable with NULLs. After reading this guide, you may feel brave enough to give the command:

 update FatPeople set Weight = nullif( Weight, -1 )

Now unknown weights will really be unknown.

Firebird 1.0: the *nullif UDFs

Firebird 1.0.x doesn't have the NULLIF internal function. Instead, it has four user-defined functions in the fbudf lib that serve the same purpose:

• inullif, for integer arguments
• i64nullif, for bigint arguments
• dnullif, for double precision arguments
• snullif, for strings

Please note that the Firebird 1.0 fbudf lib – and therefore, the *nullif function set – is only available for Windows.

Warning: The Firebird 1 Release Notes state that, because of an engine limitation, these UDFs return a zeroequivalent if the arguments are equal. This is incorrect: if the arguments have the same value, the functions all return a true NULL.

However – they also return NULL if the first argument is a real value and the second argument is NULL. This is a wrong result. The Firebird 1.5 internal NULLIF function correctly returns the first argument in such a case.

See also:
COALESCE
NULLIF

back to top of page
<< User-Defined Functions (UDFs) | Firebird Null Guide | Altering populated tables >>