Oracle NVL, NVL2, and Other Logical Function Guide, FAQ, and Examples

There are several functions in Oracle, such as NVL and NVL2, that perform some kind of logic on the values that are provided. Learn what they all do and see some examples in this article.

Oracle Logical Functions in This Guide

In this article, we’ll look at a few logical functions in Oracle:

• NVL
• NVL2
• LNNVL
• NANVL
• SIGN
• ABS
• BITAND

 

Purpose of the Oracle Logical Functions

Each of these functions has a different purpose.

NVL

The Oracle NVL function allows you to use a different value when a NULL or empty value is found.

Why would this be useful?

It’s good for translating data back to the users or systems that access it. It’s also good for aggregate functions and grouping, so you can actually see what a NULL value is.

NVL2

The NVL2 function is similar to the NVL function. NVL2 allows you to specify a value to check, and then specify a value to use if it is null, as well as a value to use if it is not null.

It’s similar to NVL because NVL allows you to check a value and return something else if it is null. NVL2 has three parameters, while NVL has two.

LNNVL

The purpose of the LNNVL is to perform a “logical not null” on the expression that has been provided.

It allows you to evaluate data that contains a NULL value, which is something you can’t easily do within a single WHERE condition.

NANVL

The NANVL function is used to return a different value if the specified value is NaN (not a number). It’s useful for floating-point numbers such as BINARY_DOUBLE and BINARY_FLOAT.

SIGN

The Oracle SIGN function is used to find the sign of a number.

The “sign” indicates whether the number is positive, negative, or zero.

This can be helpful when used with other IF logic, such as CASE or DECODE statements.

ABS

The Oracle ABS function returns the absolute value of a number.

The absolute value of a number is a number without any negative signs attached to it. So, it will always be 0 or a positive number.

BITAND

The Oracle BITAND function is used to perform what’s called a standard bitwise AND operation. It’s used to compare two numbers and outputs a third number.

I’ll explain what a bitwise AND operation is later in this article.

 

Oracle NVL Function Syntax and Parameters

The NVL function is structured like this:

NVL( check_value, replace_value )

The parameters of the NVL function are:

• check_value (mandatory): This is the value that is displayed to the user. It is also the value that is checked for NULL.
• replace_value (mandatory): This is the value that appears if the check_value is NULL.

So, first of all the check_value value is checked to see if it is NULL. If it is not NULL, it is returned by the function. If it is NULL, the replace_value is returned by the function.

If the replace_value is NULL, it is still returned. This can often happen if you use another column as the replace_value instead of a static value or string.

The Oracle NVL function can use string, date, and number values, for both the check_value and replace_value. However, they both need to be the same type. If a string is used for the check_value, a string also needs to be used for the replace_value.

 

Oracle NVL2 Function Syntax and Parameters

The syntax of the Oracle NVL2 function is:

NVL2( value_to_check, value_if_not_null, value_if_null )

The parameters of the NVL2 function are:

• value_to_check (mandatory): This is the value to check to see if it is NULL or not.
• value_if_not_null (mandatory): If value_to_check is not null, then NVL2 returns this value.
• value_if_null (mandatory): If value_to_check is null, then NVL2 returns this value.

Some notes about this function:

• These parameters are called different names by Oracle in their NVL2 function definition. The value_to_check is called expr1, the value_if_not_null is called expr2, and the value_if_null is called expr3.
• The value_to_check parameter can have any data type.
• The value_if_not_null and value_if_null can have any data type except LONG.
• The data type of value_to_check determines the output.
• If value_if_not_null  is a character type, then value_if_null is converted to the data type of value_if_not_null before comparing, unless value_if_null is a NULL constant.
• If value_if_not_null  is numeric, then Oracle determines the type with the higher precedence.

 

Oracle LNNVL Function Syntax and Parameters

The syntax of the LNNVL function is:

LNNVL (condition)

The parameters of the LNNVL function are:

• condition (mandatory): This is the condition to evaluate as TRUE or FALSE.

 

Oracle NANVL Function Syntax and Parameters

The syntax of the NANVL function is:

NANVL ( input_value, replace_with )

The parameters of the Oracle NANVL function are:

• input_value (mandatory): This is the value to be checked to see if it is NaN.
• replace_with (mandatory): If the input_value is NaN, then this value is returned. Otherwise, the input_value is returned.

The NANVL function can take any numeric data type as an input.

 

Oracle SIGN Function Syntax and Parameters

The syntax of the Oracle SIGN function is:

SIGN ( number )

The return value of the SIGN function is one of three values, if the number is a NUMBER data type:

• If the number is > 0, then SIGN returns 1.
• If the number is = 0, then SIGN returns 0.
• If the number is < 0, then SIGN returns -1.

If the number is a binary floating-point number (BINARY_FLOAT or BINARY_DOUBLE), then:

• If the number is > 0, then SIGN returns 1.
• If the number is = 0, then SIGN returns 1.
• If the number is <0, then SIGN returns -1.
• If the number is NaN (not a number), then SIGN returns 1.

So, if you can remember that SIGN returns negative 1 when the number is negative, and positive 1 when the number is positive, then you can remember which return value is which.

The return data type is NUMBER.

The parameter of the SIGN function is:

• number (mandatory): The number to be checked for its sign. It can be any numeric data type, or a data type that can be implicitly converted to NUMBER.

 

Oracle ABS Function Syntax and Parameters

The syntax of the Oracle ABS function is:

ABS( number )

The parameters of the ABS function are:

• number (mandatory): This is the number to convert into an absolute number.

The function returns the same datatype as the parameter. The function also does an implicit conversion to a number, if you provide a number inside a text string, for example. More information on this is available here.

 

Oracle BITAND Function Syntax and Parameters

The syntax of the BITAND function is:

BITAND ( expr1, expr2 )

The parameters of the BITAND function are:

• expr1 (mandatory): One of the expressions to be used in the bitwise AND operation.
• expr2 (mandatory): The other expression to be used in the bitwise AND operation.

Some other things to note about this function:

• Both expr1 and expr2 must be a number value.
• Both expr1 and expr2 must be between  -(2(n-1)) .. ((2(n-1))-1), where n=128.

 

Oracle NVL2, NVL, CASE, DECODE, or COALESCE?

Why would you use NVL2 instead of NVL, CASE, DECODE, or COALESCE?

Well, there seems to be a lot of functions that you can use to get the same result.

• NVL – Checks one value and returns it, if it is null, return another value
• NVL2 – Checks one value, if it is null, return a different value, if it is not null, return another different value
• CASE – Allows for IF-THEN-ELSE statements in SQL
• DECODE – Allows for IF-THEN-ELSE statements in SQL.
• COALESCE – Returns the first non-null expression in your list

So, it depends on what you need the function for.

In my article on the Oracle DECODE function, I recommend that you use CASE instead of DECODE for several reasons.

• If you need just a simple NULL comparison to show a different value, use NVL.
• NVL2 is an Oracle-only function, so this is something you may want to keep in mind.
• COALESCE is a standard SQL function, and also has a bit more functionality.

So, your choice will depend on what you need the function for and how much you need it to do.

For NVL2 vs CASE, I would use NVL2 only if I was checking for NULL. If it was something more complicated, I would use CASE.

For NVL2 vs DECODE, I would also use NVL2 only if I was checking for NULL, as with CASE. I would also use CASE instead of DECODE if possible.

 

Can You Use Oracle NVL Function in WHERE Clause?

Well, yes, you can use the NVL function in a WHERE clause. However, it’s generally not a good idea.

NVL functions are used to check if one value is NULL and return another. The same functionality could be done with a JOIN, and is easier to understand.

Also, if two NVLs are used (for example, WHERE NVL() = NVL()) then it could give undesired results, as a NULL on the left does not equal a NULL on the right.

 

Can You Use Oracle NVL Function For Date Values?

Yes, you can, as long as both the check_value and replace_value are the same. You may need to use a TO_DATE function if you’re specifying another date to return if the first parameter is NULL. See the examples below for how this is done.

 

How Does The Oracle LNNVL Function Work?

The LNNVL function returns either TRUE or FALSE. This table demonstrates how the return value is calculated.

Let’s use our student table, which has a value of fees_required and fees_paid. Let’s say the values for a row are:

fees_required: 1000

fees_paid: NULL

Condition	Is condition true?	LNNVL Returns
fees_required=1000	TRUE	FALSE
fees_required = 500	FALSE	TRUE
fees_required IS NULL	FALSE	TRUE
fees_paid = 100	UNKNOWN	TRUE
fees_paid IS NULL	TRUE	FALSE
fees_required = fees_paid	UNKNOWN	TRUE

You can see that it kind of gets the opposite of what is provided to it. If the condition returns TRUE, LNNVL returns FALSE.

The only exception is when null values are considered.

 

Why Whould I Use the LNNVL Function?

The main reason to use the LNNVL is if you have data that contains NULL values, and you want to use the NULL value in your logic.

Let’s say we have this student table here.

FIRST_NAME	LAST_NAME	FEES_PAID
John	Smith	100
Susan	Johnson	150
Tom	Capper	320
Mark	Holloway	410
Steven	Webber	80
Julie	Armstrong	0
Michelle	Randall	(null)
Andrew	Cooper	400
Robert	Pickering	100
Tanya	Hall	150
Jarrad	Winston	300
Mary	Taylor	100
Mark	Anderson	45
John	Rogers	700

You can see there is one record with a fees_paid value of NULL. You might want to query this table to find the names of students who have paid less than 100 of fees_paid.

Your query might look like this:

SELECT first_name, last_name, fees_paid
FROM student
WHERE fees_paid < 100;

Result:

FIRST_NAME	LAST_NAME	FEES_PAID
Steven	Webber	80
Julie	Armstrong	0
Mark	Anderson	45

You can see that your results include a few rows, but don’t include the row where fees_paid is null. This is because NULL is not evaluated as a value.

But, what if you want NULL treated like another value?

You could use NVL. But, sometimes you may not want to use NVL.

You can use LNNVL.

If you want to see all rows where the fees_paid is less than 100, and including those where fees_paid is NULL, you can use this query:

SELECT first_name, last_name, fees_paid
FROM student
WHERE LNNVL(fees_paid >= 100);

Result

FIRST_NAME	LAST_NAME	FEES_PAID
Steven	Webber	80
Julie	Armstrong	0
Michelle	Randall	(null)
Mark	Anderson	45

You can see it now shows the records where fees_paid is NULL.

So, that’s a situation where you might need to use the LNNVL function.

 

What Steps Does BITAND Take?

The steps that the BITAND function takes to get to its answer are:

1. First, expr1 and expr2 are replaced with the value of (SIGN(expr) * FLOOR(ABS(expr)), which truncates the number towards zero.
2. Then, each input is converted to a binary integer value. For example, the value of 5 is converted to 101.
3. Then, these two binary values are compared using the bitwise AND operation, and the result is returned.

 

What Is The Standard BITWISE And Operation?

I’ve mentioned this term a lot in this article, and you might have seen it if you’ve looked up other pages for this function or other functions.

But what is the bitwise AND operation?

It’s where you compare two binary strings by each number’s position, and return a string that represents the union of them.

For example, let’s say we had two binary strings:

01001011 = 75

11010110 = 214

The bitwise AND operation will look at each position in both strings, and check if they are set to 1. If they are both set to 1, then the result for that position is 1. If not, it is 0.

So, when performing the operation on these two strings, the result for the first position from the left is 0, because only one of them is set to 1. The result of the second position is set to 1, because they are both set to 1.

	128	64	32	16	8	4	2	1	Result
expr1	0	1	0	0	1	0	1	1	75
expr2	1	1	0	1	0	1	1	0	214
result	0	1	0	0	0	0	1	0	66

You can see here that the result is then converted to a number, using binary, which is 66.

So, that’s how you perform the bitwise AND operation.

 

How To Use the SIGN Function in Oracle?

To use the SIGN function, just place your NUMBER you want to check inside the brackets of the SIGN function, and put that in your query.

You may want to add additional logic to the sign of the number, as SIGN by itself may not be that useful for you.

 

Examples of the NVL Function

Here are some examples of the NVL function. I find that examples are the best way for me to learn about code, even with the explanation above.

The values in red indicate those that have been translated from NULL, so you can see where the impacted values are.

 

Example 1 – NVL with String

SELECT
first_name, last_name, country,
NVL(country, 'No country') AS country_val
FROM customers;

FIRST_NAME	LAST_NAME	COUNTRY	COUNTRY_VAL
John	Smith	USA	USA
Sally	Jones	USA	USA
Steve	Brown	Canada	Canada
Mark	Allan	UK	UK
Adam	Cooper	USA	USA
Josh	Thompson		No country
Peter	Manson	France	France

This example uses a string value for both parameters. Notice how the name of the last column has been set to the function name, which isn’t ideal. This can be changed, though.

 

Example 2 – NVL with Number

SELECT
first_name, last_name, employees,
NVL(employees, 0) AS "Fixed Employees"
FROM customers;

FIRST_NAME	LAST_NAME	EMPLOYEES	Fixed Employees
John	Smith	4	4
Sally	Jones	10	10
Steve	Brown	15	15
Mark	Allan	23	23
Adam	Cooper	55	55
Josh	Thompson	1	1
Peter	Manson		0

This example uses the NVL function with a number. It sets the value of 0 if a NULL value is found for employees. Notice how the name of the column has been updated as well, to “Fixed Employees”.

 

Example 3 – With Dates

SELECT first_name,
last_name,
start_date,
NVL(start_date, TO_DATE('01-JAN-2000')) AS "Fixed Start Date"
FROM customers;

FIRST_NAME	LAST_NAME	START_DATE	Fixed Start Date
John	Smith	12-Apr-10	12-Apr-10
Sally	Jones	4-Jul-11	4-Jul-11
Steve	Brown	21-Mar-09	21-Mar-09
Mark	Allan	1-Feb-01	1-Feb-01
Adam	Cooper		1-Jan-00
Josh	Thompson	10-Feb-12	10-Feb-12
Peter	Manson	16-Oct-12	16-Oct-12

This example uses the NVL function with date values. Notice how the TO_DATE needs to be used, as I have specified a date manually, but it needs to be converted from string to date.

 

Example 4 – Another Column Returned

SELECT
first_name, last_name, country,
NVL(country, last_name) AS country_ln
FROM customers;

FIRST_NAME	LAST_NAME	COUNTRY	COUNTRY_LN
John	Smith	USA	USA
Sally	Jones	USA	USA
Steve	Brown	Canada	Canada
Mark	Allan	UK	UK
Adam	Cooper	USA	USA
Josh	Thompson		Thompson
Peter	Manson	France	France

This example shows you can return another column if the first parameter is NULL, and not just a specific value. I’ve returned the last_name if the country is null, which might not make sense in the real world, but this is just an example.

 

Example 5 – Multiple Columns

SELECT first_name, last_name,
NVL(country, 'No country') "Country",
NVL(employees, 0) "Employees",
NVL(start_date, TO_DATE('01-JAN-2000')) "Start Date"
FROM customers;

FIRST_NAME	LAST_NAME	Country	Employees	Start Date
John	Smith	USA	4	12-Apr-10
Sally	Jones	USA	10	4-Jul-11
Steve	Brown	Canada	15	21-Mar-09
Mark	Allan	UK	23	1-Feb-01
Adam	Cooper	USA	55	1-Jan-00
Josh	Thompson	No country	1	10-Feb-12
Peter	Manson	France	0	16-Oct-12

This example uses multiple NVL functions on multiple columns, and they have all been renamed.

 

Examples of the NVL2 Function

Here are some examples of the Oracle NVL2 function to help explain how to use the NVL2 function in Oracle SQL. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example shows the basic usage of the NVL2 function.

SELECT first_name,
NVL2(first_name, first_name, 'No name provided') AS First_Name_Check
FROM customers;

Result:

FIRST_NAME	FIRST_NAME_CHECK
John	John
Sally	Sally
Steve	Steve
Mark	Mark
Adam	Adam
Josh	Josh
Peter	Peter
(null)	No name provided

All of the records have the first_name showing, except for the last record, where it has been replaced with the specified value.

 

Example 2

This example shows a similar example, but we’ve specified NULL as the third parameter, to see what happens.

SELECT first_name,
NVL2(first_name, first_name, NULL) AS First_Name_Check
FROM customers;

Result:

FIRST_NAME	FIRST_NAME_CHECK
John	John
Sally	Sally
Steve	Steve
Mark	Mark
Adam	Adam
Josh	Josh
Peter	Peter
(null)	(null)

This output shows NULL being used to replace the NULL value. No error is shown, it just doesn’t change the data.

 

Example 3

This example uses numeric data types to show the NVL2 function.

SELECT first_name, employees,
NVL2(employees, employees, 0) AS Employee_Check
FROM customers;

Result:

FIRST_NAME	EMPLOYEES	EMPLOYEE_CHECK
John	4	4
Sally	10	10
Steve	15	15
Mark	23	23
Adam	55	55
Josh	1	1
Peter	(null)	0
(null)	(null)	0

The employee_check column shows the employees, or 0 if none are found. It works in a similar way to the character examples.

 

Example 4

This example uses NVL2 with DATE values.

SELECT first_name, start_date,
NVL2(start_date, start_date, '01-JAN-2000') AS Date_Check
FROM customers;

Result:

FIRST_NAME	START_DATE	DATE_CHECK
John	12/APR/2010	12/APR/2010
Sally	04/JUL/2011	04/JUL/2011
Steve	21/MAR/2009	21/MAR/2009
Mark	01/FEB/2001	01/FEB/2001
Adam	(null)	01/JAN/2000
Josh	10/FEB/2012	10/FEB/2012
Peter	16/OCT/2012	16/OCT/2012
(null)	16/OCT/2012	16/OCT/2012

It shows the date of 1 Jan 2000 if a NULL value is found.

 

Example 5

This example uses a mix of data types. The value_to_check is a number, and the other two parameters are strings.

SELECT first_name, employees,
NVL2(employees, 'Has some employees', 'Zero') AS Employee_Check
FROM customers;

Result:

FIRST_NAME	EMPLOYEES	EMPLOYEE_CHECK
John	4	Has some employees
Sally	10	Has some employees
Steve	15	Has some employees
Mark	23	Has some employees
Adam	55	Has some employees
Josh	1	Has some employees
Peter	(null)	Zero
(null)	(null)	Zero

The output is shown as expected. Employee_check shows two different text values, depending on the value of the employees column.

 

Examples of the NANVL Function

Here are some examples of the NANVL function. I find that examples are the best way for me to learn about code, even with the explanation above.

Let’s create a table to store some data first.

CREATE TABLE nanvl_test (
num_val NUMBER(10,3),
f_val FLOAT(3),
bf_val BINARY_FLOAT,
bd_val BINARY_DOUBLE);

INSERT INTO nanvl_test (num_val, f_val, bf_val, bd_val)
VALUES (123.456, 234.567, 987.654, 135.789);

INSERT INTO nanvl_test (num_val, f_val, bf_val, bd_val)
VALUES (0, 0, 'NaN', 'NaN');

Now, let’s see our examples.

Let’s see how the NANVL function works with each of the values in the table when translating to 0.

SELECT num_val, f_val, bf_val, bd_val,
NANVL(num_val, 9999) AS nan_num,
NANVL(f_val, 9999) AS nan_f,
NANVL(bf_val, 9999) AS nan_bf,
NANVL(bd_val, 9999) AS nan_bd
FROM nanvl_test;

Result:

NUM_VAL	F_VAL	BF_VAL	BD_VAL	NAN_NUM	NAN_F	NAN_BF	NAN_BD
123.456	200	987.654	135.789	123	200	987.654	135.789
0	0	NaN	NaN	0	0	9999.0	9999.0

The first row shows the original table columns and the NANVL function used on each of these columns. The values returned by NANVL are the same as each of the original columns, because they all have a valid number value.

The second row is different. Two values are set to 0, and two are NaN (not a number). When the NANVL function runs against each of them, the two 0 values remain the same. They are unchanged.

However, the two NaN values are changed to 9999 as part of the NANVL function.

So, this example demonstrates that the NANVL function changes NaN values to other values. I could have entered any number in the second parameter other than 9999.

 

Examples of the SIGN Function

Here are some examples of the SIGN function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example shows a positive SIGN value.

SELECT SIGN(45) AS SIGN_VALUE
FROM dual;

Result:

SIGN_VALUE

1

 

Example 2

This example shows a negative SIGN value.

SELECT SIGN(-3) AS SIGN_VALUE
FROM dual;

Result:

SIGN_VALUE

-1

 

Example 3

This example shows a zero SIGN value.

SELECT SIGN(0) AS SIGN_VALUE
FROM dual;

Result:

SIGN_VALUE

0

 

Example 4

This example shows a SIGN value from a decimal.

SELECT SIGN(0.198) AS SIGN_VALUE
FROM dual;

Result:

SIGN_VALUE

1

 

Example 5

This example shows a SIGN value for a large number.

SELECT SIGN(493781.395) AS SIGN_VALUE
FROM dual;

Result:

SIGN_VALUE

1

 

Examples of the ABS Function

Example 1 – Whole Number

This example demonstrates how to use the absolute function in Oracle on a negative number.

SELECT ABS(-12) AS Absolute
FROM DUAL;

Result:

ABSOLUTE

12

The number has been converted to a positive number.

 

Example 2 – Decimal Number

This example uses the ABS function with a decimal.

SELECT ABS(-12.7) AS Absolute
FROM DUAL;

Result:

ABSOLUTE

12.7

The number has also been converted to a positive number.

 

Example 3 – Positive Number

This example uses the ABS function on a number that is already positive.

SELECT ABS(28) AS Absolute
FROM DUAL;

Result:

ABSOLUTE

28

The number has not changed.

 

Examples of the BITAND Function

Example 1

SELECT BITAND(10, 3)
FROM dual;

Result:

BITAND(10,3)

2

 

Example 2

SELECT BITAND(10, 4)
FROM dual;

Result:

BITAND(10,4)

0

 

Example 3

SELECT BITAND(451, 68)
FROM dual;

Result:

BITAND(451,68)

64

 

Example 4

SELECT BITAND(-203, 14)
FROM dual;

Result:

BITAND(-203,14)

4

 

Similar Functions

Some functions which are similar to these functions are:

• CASE – You can use the CASE statement, which provides IF THEN ELSE functionality, to do the same thing, but might not be as efficient.
• DECODE – You can also use the DECODE function to do the same thing, but might not be as efficient.
• COALESCE – Returns the first non-null expression in your list
• REPLACE – This function replaces one value inside a string with another value.
• NULLIF – Returns NULL if both parameters are equal.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!