NVL

The NVL function replaces a null value with a second value.

SQL syntax

NVL(Expression1, Expression2)

Parameters

NVL has the parameters:

Parameter	Description
Expression1	The expression whose values are to be tested for NULL, which can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, or BLOB expression.
Expression2	The alternate value to use if the value of Expression1 is NULL, which can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, or BLOB expression.

Description

• The data types of Expression1 and Expression2 must be compatible. If the data types are different, the data types are implicitly converted, if possible. If they cannot be implicitly converted, an error is returned.

  The following describes how the implicit conversion of data types is performed:

  • If Expression1 is character data, then Expression2 is converted to the same data type of Expression1 and returns the result in a VARCHAR2 in the character set of Expression1.

  • If Expression1 is numeric data, then TimesTen determines which expression has the highest numeric precedence and implicitly converts the other argument to that data type, which is also the data type that is returned.

• If Expression1 is NULL, the NVL function returns Expression2. If Expression1 is NOT NULL, the NVL function returns Expression1.

• The NVL function can be used in the WHERE or HAVING clause of SELECT, UPDATE, or DELETE statements and in the SELECT list of a SELECT statement.

Examples

This example checks for null values of commission_pct and replaces them with 'Not Applicable' for employees whose last names start with "B".

Command> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
         FROM employees 
         WHERE last_name LIKE 'B%'
         ORDER BY last_name;

< Baer, Not Applicable >
< Baida, Not Applicable >
< Banda, .1 >
< Bates, .15 >
< Bell, Not Applicable >
< Bernstein, .25 >
< Bissot, Not Applicable >
< Bloom, .2 >
< Bull, Not Applicable >
9 rows found.