2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

NULL-Related Functions

Please provide different null value handling functions
userimage

One of the most mind blowing values in the Oracle database is the NULL value. What is NULL? NULL is nothing. NULL is not even the same as NULL. NULL is undefined. But you need to work with NULL values (which are no actual values). Luckily Oracle provides us with a couple of functions to do the heavy lifting when it comes to checking for NULL's.

1) IS [NOT] NULL

Syntax: expr1 IS [NOT] NULL

Ex: SELECT *  FROM emp t WHERE t.comm IS NULL;

2) NVL

Syntax: NVL(expr1, expr2)

If expr1 contains a NULL value, then replace it with the value of expr2

The NVL function lets you substitute a value when a null value is encountered.

Ex:

NVL(‘A’,’B’) results in A

NVL(NULL,’B’) results in B

NVL(NULL,2) results in 2

NVL(1, ‘B’) results in an error

The last example results in an error because ‘B’ cannot be converted to a number. In the

one before it, it was possible to convert 2 to a varchar2 value.

3) NVL2

Syntax: NVL2(expr1, expr2, expr3)

If expr1 contains a NULL value, then return expr3. If the value of expr1 contains a non-NULL value, then return expr2.

Examples:

NVL2(‘A’,’B’,’C’) results in B

NVL2(NULL,’B’,’C’) results in C

NVL2(1,2,3) results in 2

NVL2(NULL,2,3) results in 3

4)NULLIF

Syntax: NULLIF(expr1, expr2)

NULLIF returns NULL if expr1 is equal to expr2. If they are not equal expr1 is returned.

Expressions must be of the same data type, There is no implicit conversion performed.

Examples:

NULLIF(‘A’,’B’) results in A

NULLIF(‘A’,’A’) results in NULL

NULLIF(2,3) results in 2

NULLIF(2,2) results in NULL

NULLIF(‘2’,2) results in an error

NULLIF(2,’2’) results in an error

5) COALESCE

Syntax: COALESCE(expr [, expr ]...)

The coalesce function returns the first non-NULL value of the expressions in the list. The list

must consist of at least 2 values. If all expressions evaluate to NULL then NULL is returned.

Examples:

COALESCE(‘A’,’B’,’C’) results in A

COALESCE(NULL,’B’,’C’) results in B

COALESCE(NULL,’B’,NULL) results in B

COALESCE(‘A’) results in an error

6) DECODE

The DECODE function is not specifically for handling null values, but it can be used in a similar way to the NVL function, as shown by the following example.

Example : 

SELECT empno,DECODE(comm, NULL, 0, comm)AS output FROM emp ;

7) LNNVL

The LNNVL function has been available since at least Oracle 9i, but was undocumented until Oracle 11g. It is used in a where clause to evaluate a condition. If this condition evaluates to false or unknown, it returns true. If the condition evaluates to true, it returns false.

Example : SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;

        ID COL3

---------- ----------

         1 THREE

Answer is