Search results
This tutorial shows you how to use the Oracle NVL() function to substitute null with a more meaningful alternative.
NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. The arguments expr1 and expr2 can have any data type.
Oracle NVL2 () function overview. The Oracle NVL2 () function is an extension of the NVL () function with different options based on whether a NULL value exists. The Oracle NVL2 () function accepts three arguments. If the first argument is not null, then it returns the second argument.
Example. Let's look at some Oracle NVL function examples and explore how to use the NVL function in Oracle/PLSQL. For example: SELECT NVL(supplier_city, 'n/a') FROM suppliers; The SQL statement above would return 'n/a' if the supplier_city field contained a null value.
Primary Author: Usha Krishnamurthy. Contributors: Mary Beth Roeser, Drew Adams, Lance Ashdown, Vikas Arora, Thomas Baby, Hermann Baer, Yasin Baskan, Nigel Bayliss ...
The NVL function is most commonly used to substitute a default value when a column is NULL. Otherwise, the column value itself is returned. The next example shows the ID of each employee's manager, but substitutes the word 'NONE' when no manager has been assigned (i.e., when manager_emp_id is NULL):
Example. Let's look at some Oracle NVL2 function examples and explore how to use the NVL2 function in Oracle/PLSQL. For example: select NVL2(supplier_city, 'Completed', 'n/a') from suppliers; The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.
