OracleDBNVL, NVL2, NULLIF, COALESCE Functions

OracleDBNVL, NVL2, NULLIF, COALESCE Functions

Oracle db nvl, NVL2, NULLIF, COALESCE Functions

Regular Functions

The following functions are applicable to any data type and use null values:

• NVL (expr1, expr2)

• NVL2 (expr1, expr2, expr3)

• NULLIF (expr1, expr2)

• COALESCE (expr1, expr2,..., exprn)

The following functions are applicable to any data type and use null values in the expression list.

NVL Function

Convert null values to actual values:

• The data types available are date, character, and number.

• Data Types must match:

-NVL (commission_pct, 0)

-NVL (hire_date, '01-JAN-97 ')

-NVL (job_id, 'nojob yun ')

To convert a null value to an actual value, use the NVL function.

Syntax

NVL (expr1, expr2)

In this syntax:

• Expr1 is a source value or expression that may contain null values.

• Expr2 is the target value used to convert null values.

You can use the NVL function to convert any data type, but the returned value always has the same data type as expr1.

NVL conversion of various data types

Use NVL Functions

LAST_NAME salary nvl (COMMISSION_PCT, 0) AN_SAL

------------------------------------------------------------------

OConnell 2600 0 31200

Khoo 3100 0 37200

Vargas 2500 0 30000

Russell 14000. 4 235200

Bernstein 9500. 25 142500

To calculate the annual compensation for all employees, multiply the monthly salary by 12 and then add the Commission percentage:

Hr @ TEST0924> SELECT last_name, salary, commission_pct, (salary * 12) + (salary * 12 * commission_pct) AN_SAL FROM employees;

LAST_NAME SALARY COMMISSION_PCT AN_SAL

-----------------------------------------------------------

OConnell 2600

Vargas 2500

Russell 14000. 4 235200

Taylor 3200

Please note that the annual compensation is calculated only for the employees who receive the Commission. If any column value in the expression is null, the result is also null. To calculate the annual compensation for all employees, the null value must be converted to a number before arithmetic operators can be applied. In this example, the NVL function is used to convert null values to zero values.

For more details, please continue to read the highlights on the next page:

Related reading:

Oracle COALESCE Functions

Common Oracle functions: nvl/NULLIF/case when/wm_concat/replace

Oracle NULLIF Function

Use of Oracle NULLIF Functions

Introduction to Oracle nvl () and NVL2 () Functions

NVL functions in Oracle

About NVL functions in Oracle databases