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

 

NO SAL
1 2000
2 3000
3 NULL
4 NULL
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.

Advertisements

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.

1 thought on “Oracle NVL function”

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s