Purpose
NVL
lets you replace null (returned as a blank) with a string in the results of a query. If expr1
is null, then NVL
returns expr2
. If expr1
is not null, then NVL
returns expr1
.
The arguments expr1
and expr2
can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. The implicit conversion is implemented as follows:
-
If
expr1
is character data, then Oracle Database convertsexpr2
to the datatype ofexpr1
before comparing them and returnsVARCHAR2
in the character set ofexpr1
. -
If
expr1
is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.