NVL
The NVL (Null Value Replacement) function tests the values returned by an expression. If the value returned is NULL, the function replaces the NULL value with the new value. If the value returned is not NULL, it is left unchanged.
Syntax
NVL (expression, new_value)
Remarks
• You can replace NULL values in a column with a value of a compatible data type.
• NVL treats empty strings as NULL. For example, NVL (nullString, '') returns NULL.
• NVL returns NULL when expression is an empty string.
• DATE and TIMESTAMP cannot be used in the same NVL command.
• NVL follows the ANSI SQL rules for data type precedence.
Example (Simple Substitution for Null Value)
SELECT NVL (ColumnName, 'N/A') FROM table
For the SELECT above, NULL values in ColumnName are replaced with the string N/A. If the input value were a column of INTEGER type, the replacement value should be an integer, and so on.
Example (Multiple NVL Function Calls)
TDV lets you issue multiple NVL function calls to replace NULL values in multiple columns. In the following example, NULL values from ColumnA are replaced with the string valueX, and NULL values from ColumnB are replaced with the value from ColumnC:
SELECT NVL (ColumnA, 'valueX'), NVL (ColumnB, "ColumnC") FROM table
The double-quotes explicitly define a column name, but the quotes can be omitted.
Example (Filtering and NVL Function Calls)
You can filter the returned result set by using the DISTINCT keyword, but it must occur outside of the NVL function call.
SELECT DISTINCT NVL (ColumnName, UniqueValue) FROM table
In the query above, all NULL values in ColumnName are replaced with UniqueValue. Because of the keyword DISTINCT, the SELECT statement returns only the first occurrence of UniqueValue.
Example (Substitution for Null Values in a Column with Values from Another Column)
Null values in one column can be replaced by the values from another column.
SELECT NVL (FormalTitle, Common_Name) FROM table
In the query above, NULL values in FormalTitle are replaced by the corresponding values from Common_Name.