The NVL and NVL2 functions in DB2

The NVL function is a null-value conversion function

NVL (Expression 1, Expression 2)

If expression 1 is a null value, NVL returns the value of expression 2, otherwise returns the value of expression 1.
The purpose of this function is to convert a null value (NULL) into an actual value. The value of its expression can be numeric, character, and date. But the data type of expression 1 and expression 2 must be the same type.

• For digital type: NVL (a,0);
• For character type: NVL (To_char (a), ' Zifeiy ')
• For date type: NVL (mydate, ' 31-dec-99 ')

Practical Examples:
Query for an employee's annual salary, if empty, replace it with 0

select (sal+nvl(comm,0))*12fromwhere emp.pno=:pno;

Here, Comm is an undefined variable that refers to a numeric type. NVL returns to 0.

NVL2 (Expression 1, Expression 2, Expression 3)

If expression 1 is empty, the return value is the value of expression 3. If expression 1 is not empty, the return value is the value of expression 2.
For example:

NVL2（comm，‘sal+comm‘,sal)

NVL2 function Test Comm
If comm is empty, the value of the SAL is returned. If Comm is not empty (null), the value of the expression Sal+comm is returned.

The NVL and NVL2 functions in DB2