All about NULL

All Oracle NULL related Functions

all_oracle_null_related_functions

NULLIF function

The NULLIF function returns a NULL value if both parameters are equal in value. If the parameters are not equal, it returns the value of the first parameter. The following query would return NULL.

SELECT NULLIF(1,1) FROM dual;

COALESCE function

The COALESCE function returns the first non-NULL value in an expression list. If all expressions are null it returns NULL. The following query would return '5'.

SELECT COALESCE(NULL, NULL, '5') FROM dual;

NVL function

The Oracle/PLSQL NVL function Converts NULL value to actual value.The following query will return 5.

Syntax:
       NVL( value, replace_if_value_is_null )

SELECT NVL(NULL,5) FROM dual;

NVL2 function

The Oracle/PLSQL NVL2 function extends the functionality found in the NVL functions. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

If expr1 is not NULL, NVL2 returns expr2. If expr1 is NULL, NVL2 returns expr3. The argument expr1 can have any data type.

Syntax:
       NVL2( expr1,  value_if_expr1_not_null, value_if_expr1_null )

SELECT NVL2(NULL,5,7) FROM dual;

Output : 7 

SELECT NVL2('dEexams',5,7) FROM dual;

Output : 5

LNNVL function

The Oracle/PLSQL LNNVL function is used in the WHERE clause of a SQL statement to evaluate a condition when one of the operands may contain a NULL value.

Syntax:
       LNNVL( condition )

Returns of LNNVL

Condition Evaluates To LNNVL Return Value
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE

Example:

Consider the product table with below data.

prod_id qty sale
1 100 NULL
2 200 300
3 300 200
4 400 100

Now if we wanted to find all the product whose qty was below their sale.The SQL will be as follows .

SELECT * FROM product WHERE qty < sale;

Output :
         prod_id     qty      sale
         2           200      300

However, if we wanted to see the products that were below their sale as well as NULL sales, we would use the LNNVL functions as follows:

SELECT * FROM product WHERE LNNVL(qty >= sale) ;

Output :
         prod_id     qty      sale
         1           100      NULL
         2           200      300

NANVL function

The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0.

Syntax:
       NANVL( value, replace_with )

SELECT NANVL(binary1,0) FROM test;

Output : The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value. 

SELECT NANVL(binary1,NULL) FROM test;

Output : The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.