Oracle NANVL() function
Description
This function is used to return an alternative value n1 if the input value n2 is NaN (not a number), and returns n2 if n2 is not NaN. This function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE.
The function takes any numeric or nonnumeric data type (can be implicitly converted to a numeric data type) as an argument.
If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise, the function returns the same numeric data type as the argument
Syntax :
NANVL(n2, n1)
Example:
We have a sample table float_point_test with three columns dec_num type 'NUMBER(10,2)', bin_double type 'BINARY_DOUB LE' and bin_float type 'BINARY_FLOAT'. Here is the table.
SQL> SELECT * FROM float_point_test; DEC_NUM BIN_DOUBLE BIN_FLOAT ---------- ---------- ---------- 1513.67 1.514E+003 1.514E+003
Now insert a row into the float_point_test table and brows the table. Here is the command below.
SQL> INSERT INTO float_point_test VALUES (0,'NaN','NaN');
1 row created.
SQL> SELECT * FROM float_point_test;
DEC_NUM BIN_DOUBLE BIN_FLOAT
---------- ---------- ----------
1513.67 1.514E+003 1.514E+003
0 Nan Nan
The example below returns bin_float if it is a number. Otherwise, 0 is returned.
SELECT bin_float, NANVL(bin_float,0)
FROM float_point_test;
Here is the result.
BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
1.514E+003 1.514E+003
Nan 0
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook