The NVL function

You can use the NVL function to convert an expression that evaluates to NULL to a value that you specify. The NVL function accepts two arguments: the first argument takes the name of the expression to be evaluated; the second argument specifies the value that the function returns when the first argument evaluates to NULL. If the first argument does not evaluate to NULL, the function returns the value of the first argument. Suppose a student table exists that includes name and address columns. Suppose also that execution of the following query on the student table returns the rows that the result shows.

Figure 1. Query

SELECT name, address FROM student;

Figure 2. Query result

name               address

John Smith         333 Vista Drive
Lauren Collier     1129 Greenridge Street
Fred Frith         NULL
Susan Jordan       NULL

The following query includes the NVL function, which returns a new value for each row in the table where the address column contains a NULL value.

Figure 3. Query

SELECT name, NVL(address, "address is unknown") AS address 
   FROM student;

Figure 4. Query result

name               address

John Smith         333 Vista Drive
Lauren Collier     1129 Greenridge Street
Fred Frith         address is unknown
Susan Jordan       address is unknown

You can specify any data type for the arguments of the NVL function provided that the two arguments evaluate to a common compatible data type.

If both arguments of the NVL function evaluate to NULL, the function returns NULL.

HCL Informix® also supports the NULLIF function, which resembles the NVL function, but has different semantics. NULLIF returns NULL if its two arguments are equal, or returns its first argument if its arguments are not equal. For more information about the NULLIF function, see the HCL Informix Guide to SQL: Syntax.