Skip to main content

nvl2

1. NVL2 Function​

The NVL2 function in H2 database is used to return different values based on whether an expression is null or not.

2. Syntax​

The syntax for the NVL2 function in H2 database is as follows:

NVL2(expr1, expr2, expr3)

Arguments​

  • expr1: The expression to be checked for null.
  • expr2: The value to be returned if expr1 is not null.
  • expr3: The value to be returned if expr1 is null.

Return​

  • The NVL2 function returns expr2 if expr1 is not null, otherwise it returns expr3.

3. Notes​

  • The NVL2 function in H2 database can be used to handle the case when a particular value is null and you want to substitute it with another value.
  • The return type of the NVL2 function is determined by the data types of expr2 and expr3. They should have compatible data types.

4. Examples​

Here are a few examples demonstrating the usage of the NVL2 function in H2 database:

Example 1 - Returning different values based on null condition:​

SELECT NVL2(NULL, 'Not Null', 'Null') AS result;

Output:

result
------
Null

Example 2 - Returning different values based on null condition in a column:​

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DOUBLE
);

INSERT INTO employees VALUES (1, 'John Doe', NULL, 5000),
                             (2, 'Jane Smith', 35, 6000),
                             (3, 'Mark Johnson', NULL, 5500);

SELECT id, name, NVL2(age, 'Age Available', 'Age Not Available') AS age_status
FROM employees;

Output:

id |     name      |       age_status
---+---------------+-------------------------
1  | John Doe      | Age Not Available
2  | Jane Smith    | Age Available
3  | Mark Johnson  | Age Not Available
  • NVL - Replace null values with a specified value.
  • coalesce - Return the first non-null expression among a list of expressions.