Try it here
Subscribe
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.

Writer profile pic

Uk01 on Apr 20, 2015 at 12:02 am


If you like dEexams.com and would like to contribute, you can 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.



Post Comment

Comments( 0)

×

Forgot Password

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