SQL statement NVL () usage

A 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 (comm,0);

For character type NVL (To_char (comm), ' No Commission ')

For date type NVL (HireDate, ' 31-dec-99 ')

Example:

Select ENAME,NVL (To_char (comm), ename| | ' is not a salesperson! ') As COMMISSION

From EMP

Two 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.

SQL statement NVL () usage