Firebird Documentation Index → Firebird Null Guide → Converting to and from NULL |
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 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
functions take exactly two
arguments. Like *NVL
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.
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.
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
NULL
s. 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.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.
The Firebird 1 Release Notes state that, because of an engine limitation, these UDFs
return a zero-equivalent 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.
Firebird Documentation Index → Firebird Null Guide → Converting to and from NULL |