EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle NVL()

Oracle NVL()

Priya Pedamkar
Article byPriya Pedamkar

Oracle NVL()

Introduction to Oracle NVL()

The Oracle NVL () function in Oracle can be defined as a function which allows the user to replace the null value or an empty string in the result set with a better alternative like a string value and this function returns the not non value of the same data type of the base expression in case the value is not null and it replaces the null or empty string with a different alternative (passed as an argument with the function) in case the original value is null.

Syntax:.

ADVERTISEMENT
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

NVL(expression, replacement expression)

Parameters:

  • expression: It refers to the expression which we want to replace in case it is null.
  • replacement expression: It refers to the value with which we will replace the null value.

How NVL() Function work in Oracle?

Here we can see in the syntax of the function that it takes two arguments. The first one is a field name or expression and the second one is a value which will replace the null value. So when a statement having this query is executed in Oracle. The function immediately checks the values of the field or expression in all the rows returned by the select statement.

If the value of the field or column is not null then it will return the original value but in case it finds any one of the row in the column having a null value then it replaces that particular value with whatever value we have given in the second parameter of the function. It then returns the modified result set as output.

Examples of Oracle NVL()

Given below are the examples of Oracle NVL():

Example #1

Replace null value with zero using NVL function.

In this scenario we will try to replace the age of the employees which are null with zero so that the null values in age column of the employee table are replaced with the number zero. The NVL function will only be applied on the age column.

Let us look at the table records before the NVL function is applied.

Code:

select * from employee;

Output:

Oracle NVL() 1

As we can see in the age column there is a null value . So let us now write a query to replace the null value with number 0.

Code:

select employee_id, name, NVL(age, '0') from employee;

Output:

Now we will run the query in SQL developer and look the result.

Oracle NVL() 2

As we can see in the above screen shot that the null value in age column has been replaced with number zero.

Example #2

Replace a null value with a string.

In this scenario we are going to replace a null value of a field with a string. In the example for this scenario we are going to try to replace the null value present in the city column of the employee table with a string ‘NOT AVAILABLE’ using NVL function.

Let us look at the employee table data before we apply NVL function.

Code:

select * from employee;

Output:

Oracle NVL() 3

As we can see in the employee table data the city column has a null value.

Let us now replace the null value in the city column with a ‘NOT AVAILABLE’ string.

Code:

select employee_id, name, NVL(city, 'NOT AVAILABLE') from employee;

Output:

Let us now execute the query and check the result in SQL developer.

value with a string.

As we can see in the output above the null value in the city column has been replaced by the string ‘NOT AVAILABLE’.

Example #3

Replace a null value with a value from another field.

In this scenario we are going to replace the null value of one field with value from another field or column.

In this example we will replace the null value stored in the city column of the employees table with the value of vehicle_name column of the same row.

Let us look at the table data before applying the NVL function.

Code:

select * from employee;

Output:

Oracle NVL() 5JPG

As we can see in the above screen shot that the null value in the city column will be replaced by the value ‘Tata’ present under the field vehicle_name in the same row.

Let us now write a query for the same using NVL function.

Code:

select employee_id, name, vehicle_name, NVL(city, vehicle_name) from employee;

Output:

Let us now run the query in SQL developer and look at the result.

null value with a value from another field.

As we can see in the above screen shot that the null value has been replaced by the value ‘Tata’ which is a value in the column vehicle_name present in the same row.

Example #4

Using distinct function with NVL function.

So we all are aware that DISTINCT function is used to return the unique values present in a field. In this example we are going to find the distinct cities present in the city column of the table employee. We are going to use NVL because we have a null value present in the city column and we are going to replace that using NVL function as distinct function will return null also as a unique value.

Let us look at the data before applying NVL function.

Code:

select * from employee;

Output:

Oracle NVL() 7JPG

Let us now write a query to find the distinct values and replace the null value with the city ‘Bhopal’.

Code:

select DISTINCT( NVL(city, 'Bhopal')) from employee;

Output:

Let us run the query in SQL developer and check the result.

replace the null value with the city ‘Bhopal

As we can see in the output the null value has been replaced by ‘Bhopal’ and the query returns the distinct cities in city column.

Conclusion

In this article we have seen about the definition of NVL function and its syntax. We also saw working of the function in Oracle and later on in the article we went through various scenarios along with examples.

Recommended Articles

This is a guide to Oracle NVL(). Here we discuss the introduction, how NVL() function work in oracle? and examples respectively. You may also have a look at the following articles to learn more –

  1. Oracle While Loop
  2. Oracle Window Functions
  3. Oracle LENGTH()
  4. Oracle ROLLBACK
ADVERTISEMENT
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW