Oracle Conversion Function - Oracle/PLSQL NULLIF Function

This Oracle tutorial explains how to use the Oracle/PLSQL NULLIF function.

NULLIF(e1, e2) returns NULL if e1 and e2 are the same. Otherwise, it returns e1. e1 and e2 must have the same datatype.

Syntax

The syntax for the Oracle/PLSQL NULLIF function is:

NULLIF( expr1, expr2 )

expr1 and expr2 must be either numeric values or values that are of the same datatype.

expr1 can be an expression that evaluates to NULL, but it can not be the literal NULL.

Example


SQL> SELECT NULLIF(1,null) FROM   dual;
-- w  w  w. j  a  va 2  s. c  om
NULLIF(1,NULL)
--------------
             1

SQL>
SQL> SELECT NULLIF('1','1') FROM   dual;

N
-


SQL>
SQL> SELECT NULLIF(1,1) FROM   dual;

NULLIF(1,1)
-----------


SQL>
SQL>
SQL> SELECT NULLIF(1,'1') FROM   dual;
SELECT NULLIF(1,'1') FROM   dual
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR


SQL>
SQL>
SQL> SELECT NULLIF(null,null) FROM   dual;
SELECT NULLIF(null,null) FROM   dual
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR


SQL>