In MariaDB, the NVL2() function allows us to replace a value with another value, the new value being determined by whether or not the initial value is null.

It’s similar to the NVL() function, except that NVL2() accepts three arguments instead of two. This allows us to specify a different value to return in the event the first argument is not null.

Syntax

The syntax goes like this:

NVL2(expr1, expr2, expr3)

If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

Example

Here’s an example to demonstrate:

SELECT NVL2(null, 'Cat', 'Dog');

Result:

Dog

In this case, the first argument was null and so the third argument was returned.

And here’s what happens when the first argument is not null:

SELECT NVL2('Mouse', 'Cat', 'Dog');

Result:

Cat

When the Replacement Values are Null

If the second or third argument is null, then you could end up with a null result.

Example:

SELECT 
  NVL2('Mouse', null, 'Dog') AS result_1,
  NVL2(null, 'Cat', null) AS result_2;

Result:

result_1	result_2
--------------- --------
NULL	        NULL

Invalid Argument Count

Calling the function without passing any arguments results in an error:

SELECT NVL2();

Result:

ERROR 1582 (42000) at line 1: Incorrect parameter count in the call to native function 'NVL2'

And passing too many arguments also causes an error:

SELECT NVL2(1, 2, 3, 4);

Result:

ERROR 1582 (42000) at line 1: Incorrect parameter count in the call to native function 'NVL2'