SQL NVL function
SQL NVL function is used to substitute a value when a null value is encountered. The syntax for the NVL function is:
NVL( string_to_be_tested, replace_with_if_null )
First attribute is the string which has to be tested for null values, if the string turns out to be a null value it will be replaced with value of second attribute. Both parameters of the SQL NVL function have to be of the same data type. You cannot use this function to replace a null integer by a string unless you call the TO_CHAR function on that value:
NVL(TO_CHAR(numeric_column), 'some string');
SQL NVL function is very useful when we have perform some arithmetic function on column values which do not allow null value, in that case we can simply replace null values with zero value.
Example:
The following query uses SQL NVL function to replace any null value for hire_date with current date:
SELECT first_name, NVL(hire_date, SYSDATE) FROM employee;
Output:
Note: SYSDATE is a function which returns current date and time.
Example 2:
The following example uses SQL NVL function to replace null values in mgr_id column with ‘No Manager’ for employee table
SELECT full_name, NVL(TO_CHAR(mgr_id), ‘No Manager’) FROM employee;
Output:
Note: this query is using to_char function to convert mgr_id to string value.