Oracle NVL function

The NVL used to replace NULL value with another value. To know more about NULL read post Oracle NULL

Syntax:-

NVL(expression,replace_value)

Note:- expression and replace_value must be compatible data types.

Examples :-

Consider below table SALARY.

Name      Type
———-   ————
NO          NUMBER
SAL         NUMBER

1) Applying NVL to sal column

select no,NVL(sal,0) from salary;

NO     NVL(SAL,0)
———-  —————-
1           2000
2           3000
3           0
4           0
5           1000

2)  NVL with in-compatible data type not allowed

select NVL(sal,’no salary’) from salary where no =3;

ERROR at line 1:
ORA-01722: invalid number

Reason : sal column is number and trying to convert to string.

It is always a good practice to apply NVL for null columns.

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated. View all posts by sqlandplsql