Oracle NVL function

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



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

Examples :-

Consider below table SALARY.

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


1 2000
2 3000
5 1000

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.

One thought on “Oracle NVL function

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s