• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » NVL() function in Oracle

NVL() function in Oracle

April 10, 2020 by techgoeasy Leave a Comment


This Oracle SQL tutorial provides explanations, examples for NVL function in Oracle

NVL function in Oracle is a nesting function which is used to substitute null values with certain values

NVL function in Oracle

In the above syntax

expr1 : it is the source values or expression which may contain null. You can provide column name,function on the column name
expr2 : It is the target value which will be placed in case null is returned from expr1

If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

Important Points to note
(1) The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.
If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

So following statement would succeed

select nvl(num-col ,10) from <table>
select nvl(char-col ,'NA') from <table>
select nvl(end_date,'01-MAY-18') from <table>

Following statement would do implicit conversion

select nvl(char-col ,1) from <table>

Following statement would fail

select nvl(end_date,'m/a') from <table>
select nvl(user_id,'abc') from <table>

Usage
This function is very useful where we dont want to return null values or in numeric calculation as null would turn whole values into null

Lets take an example to understand it
We have sales_people table which contain the sales done’s by sales people,commission % on the sales ,salary of the people. We want to calculate total monthly incomes for the sales people. Sales can have null values

Statement would be

select name,(salary + sales*commission/100)  Monthy_income from sales_people;

It will return null values of all the people who has not done any sales in that period as calculation will have null values

This can be fixed by using nvl function as

select name,(salary + nvl(sales,0)*commission/100) Monthly_income from sales_people;

So it is clearly very helpful while doing Mathematical operations. NVL Function in Oracle is quite a useful function and it can be used in many places

Also Reads
oracle sql date functions
NULLIF function in Oracle
Update statement in oracle
Coalesce Function in Oracle
change date format in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm


Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Multi Language Support in 11i/R12
  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us