Null Functions in Oracle SQL

NULL is interesting in SQL. NULL , you can say, its just Blank. Then whats the difference between an Empty String and a NULL.

Well, Oracle now changed its behavior and consider "empty String" as 'null'. Let me show you some queries from the Database -

Note -  dump() : Now Dump(expr) is a function, which will return the data type code, length of the notation ( in bytes ) and the interpreted form of the expression. Here is the syntax below -
Use - One of the main uses we do, when we want to know, if there is any non-printable characters in the String.
dump(expr, return_format, start_position, length), out of which except "expr", all are optional.
Return_format - There are couple of return formats, which you can specify, although, as a developer, we rarely used in day-to-day activities
8 - Octal Representation
10 - Decimal Representation
16 - Hexadecimal Representation
17 - Single Numbers

If we add 1000 with any of the above, it will display Representation along with Character Set Name. See the examples below -

Coming back to the original topic, see the below results, we tried to find out the datatype code , ASCII values of NULL and Empty String. Both the results are same -



Hope, now, some confusions are clear !! Lets start with SQL Row functions related to NULL -

• NVL - NVL takes two parameter and  checks if the value of the first parameter is NULL or not. If found NULL, it returns the value in the second parameter. For example, see the following screenshot -

Here, since the first parameter 'Johny' is not null, NVL returns the value of the first parameter itself. Now, lets see the next example, where first parameter would be null. See below -


Here, NVL just returns the value of the second parameter, since first parameter is null. Hope, its now clear.

• NVL2 - NVL2 accepts 3 parameters and checks if the first parameter is null or not. If found null, it returns the third parameter, otherwise, if found not null, returns the second parameter.

Lets see the example -


Hence, you can see, the function NVL as well as NVL2 is dependent on the first parameter value , irrespective of any value in other parameters.
Differences between NVL and NVL2

1. NVL accepts 2 parameters, while NVL2 accepts 3 parameters.
2. In case of NVL, it returns the value of the second parameter, if first parameter is null. But in case of NVL2, it returns the value of third parameter, if first parameter is null.
3. In NVL, first parameter is returned, while not null which is very different from NVL2 as first parameter is never returned in NVL2.

• COALESCE -  COALESCE accepts two or more than two parameters and return the first non-null parameter to the user. If all the parameters are NULL, 'Coalesce' return NULL. Lets see the below example  -

• NULLIF - 'NullIf' function accepts two parameters and return null, if both the parameters are equal, otherwise, it returns the first parameter value. Lets see the example -

• LNNVL -  Now, this something interesting. I am not talking about documentation of the function to Oracle versions here.

'LNNVL' is used to evaluate a condition in 'where' clause.  When the condition is true , it returns 'FALSE' as Boolean. But, when condition is 'false', it returns 'TRUE' as boolean. Lets see the example -


See, in the second query, it returns nothing, ( or it evaluated to boolean 'false') , as condition in the 'where' clause is true i.e. 2=2.

• NANVL - This is little bit of confusing function, seems at first.

'NANVL' function is used to check, if the first parameter is 'NaN' or 'Not a Number' value, and will return NULL, if second parameter specified as 'NULL'.
But, lets say first parameter is 'NaN' and second parameter is a 'Number', this will return the first parameter by explicitly converting it to a Number. This is because, NANVL, only takes Numeric value or DataTypes that can be converted to a Numeric data type in the parameters.
Lets see the example -


Originally, 'NANVL' is used to determine , 'NaN' value in 'Binary_Float' or 'Binary_Double' Numbers.

• Sys_op_map_nonnull -  This is a beautiful function, to match null values.

You probably already know, we can't match NULL = NULL in the query. Look at the example below -

But, the function Sys_op_map_nonnull, gives that functionality to match NULL with Equality Operator. This is an alternative to 'is' clause that can be utilize.  See below  -

However, we can also achieve this using "NULL is NULL", with 'is' clause.


That's it for the NULL Functions. We will discuss the "Analytical" functions in a separate Post.
Good Luck !!
Any comments is appreciable. Thank You !