Hello guys,
In this blog, we will talk about Null related functions. In Oracle documentation these functions are categorised under Single-Row Functions.
What are the Null related functions?
Null related functions are those functions which let user handle Null values in the query output.
It means if these are some null values in any column, we can replace null values with anything we want.
In order to learn the use of null related functions, we will start with NVL and then NVL2.
NVL Function
Syntax of NVL function is pretty simple.
Syntax:
NVL(expr1, expr2)
In the above syntax, expr1 and expr2 are columns from the table. In some cases, expr2 can be a char value.
If expr1 is null then NVL returns expr2 and if expr1 is not null then NVL returns expr1.
expr1 and expr2 can have any data type. Oracle engine can convert data type of expr2 implicitly to expr1 data type or sometimes it produces an error.
The implicit conversion is implemented as follows:
• If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
• If expr1 is numeric, then Oracle Database raise an error.
Let’s connect to HR schema of our Oracle database.
SELECT last_name, NVL(commission_pct, salary) commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;
LAST_NAME COMMISSION
------------------------- ----------
Baer 10000
Baida 2900
Banda .1
Bates .15
Bell 4000
Bernstein .25
Bissot 3300
Bloom .2
Bull 4100
In the above sql, commission_pct and salary have the same data type in the table. So where commission_pct column does not have any value or have Null value, NVL function is retuning values from the Salary column of the table.
Let’s try this function with different data types.
SELECT last_name, NVL(commission_pct, 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;
Output:
Error starting at line : 1 in command -
SELECT last_name, NVL(commission_pct, 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name
Error report -
ORA-01722: invalid number
Above SQL query will return an error because data type of expr2 is not matching with expr1 and Oracle engine is not able to convert data type implicitly.
To overcome this error, we need to add an addition conversion function to change the data type of commission_pct column to the data type of expr2.
SELECT last_name, NVL(to_char(commission_pct), 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;
Output:
LAST_NAME COMMISSION
------------------------- ----------------------------------------
Baer Not Applicable
Baida Not Applicable
Banda .1
Bates .15
Bell Not Applicable
Bernstein .25
Bissot Not Applicable
Bloom .2
Bull Not Applicable
Note:
TO_CHAR() function is used to convert any data type to VARCHAR2 data type. I will publish a detailed blog separately on the conversion functions and introduce this function there.
Home work:
Create a table with some data where a column with VARCHAR2 data type has some NULL values and then use NVL function to display numeric values in that column.
NVL2 Function
Syntax of NVL2 function is little different than NVL function.
Syntax:
NVL2(expr1, expr2, expr3)
In the above syntax, expr1 must be a column name and expr2 and expr3 can be columns from the table or any values of any data type.
If expr1 is not null then NVL2 function will return expr2 and if expr1 is null then it will return expr3.
Data type conversion rules are same as NVL function.
Let’s do an example and see how it works.
SELECT last_name, salary,commission_pct,
NVL2(commission_pct, salary + (salary * commission_pct), salary) income
FROM employees
WHERE last_name like 'B%'
ORDER BY last_name;
Output:
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- ----------
Baer 10000 10000
Baida 2900 2900
Banda 6200 .1 6820
Bates 7300 .15 8395
Bell 4000 4000
Bernstein 9500 .25 11875
Bissot 3300 3300
Bloom 10000 .2 12000
Bull 4100 4100
You can see in the output that where commission_pct (expr1) is null, Oracle database is showing Salary (expr3) as Income and where commission_pct (expr1) is not null, Oracle database is calculating expr2 and showing as Income.
That’s all for NVL and NVL2 function. Try more examples with different data types to have more understanding of NVL and NVL2 fuctions.
If you have any questions please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group.
Thank you,
Kapil
