A null value is a field that has been left blank or has no assigned value.
NULL values are common in databases, especially when importing external data. Therefore, unless a column contains a constraint to prevent NULL values, the database engine will likely use a NULL value for a missing value.
Although NULL values are common, they can lead to database issues as the database has no value to work with. It is, therefore, a good practice to apply actions to handle NULL values in your Queries.
In this post, we will learn how to convert NULL values in a given table to contain a default value that is less likely to cause operation errors in your database.
Oracle NVL Function
As mentioned, this function allows us to substitute NULL values with more meaningful values. The function syntax is provided below:
The function accepts two main arguments:
expr1 and expr2 – this parameter defines the value to be tested for NULL values. If the value of expr1 is NULL, the function will return the value of expr2.
Both expressions can have similar or different data types. If the corresponding data types differ, the database engine may perform implicit conversion to allow compatibility with the data types. If it’s impossible to convert the corresponding data types, the database engine will throw an error.
Oracle NVL() Function Example Usage
The following example demonstrates the basic usage of the NVL() function:
Example 1
Consider the following example:
In the previous example, we use the NVL() function to test whether the string ‘hello’ is a NULL value. Since the provided value is not null, the function will return the string ‘hello’.
Example 2
Consider the second example shown below:
In this case, since the first expression is a NULL value, the query will return the second string as shown below:
Example 3
We can also use the NVL() function to replace NULl values in a database table. Consider the employees’ table shown below:
Resulting table:
As we can see, the commission_pct column contains NULl values. We can create a query to replace the NULL values from the commission_pct column with 0, as shown in the example below:
from EMPLOYEES emp;
In the previous example query, we use the NVL() function to test the value in the commission_pct column for NULLs. If there is a NULL value, we return 0. Otherwise, we return the original value.
This should allow us to replace the NULL values in the column with 0, as shown in the resulting table below:
We can use this with an UPDATE statement or store the resulting value in a table view.
Conclusion
In this tutorial, you learned how to use the Oracle NVL() function to replace NULL values with default values. It is good to keep in mind that although the NVL() function closely resembles the COALESCE() function, the NVL() function can only return a single value. At the same time, the coalesce() function can return multiple values.
If you are looking for a function that can take more than two arguments without using the coalesce() function, consider the NVL2() function.
