Oracle null check function

It then returns the modified result set as output. In this scenario we will try to replace the age of the employees which are null with zero so that the null values in age column of the employee table are replaced with the number zero. The NVL function will only be applied on the age column. As we can see in the age column there is a null value.

So let us now write a query to replace the null value with number 0. As we can see in the above screen shot that the null value in age column has been replaced with number zero. In this scenario we are going to replace a null value of a field with a string. In this scenario we are going to replace the null value of one field with value from another field or column. In this example we are going to find the distinct cities present in the city column of the table employee. We are going to use NVL because we have a null value present in the city column and we are going to replace that using NVL function as distinct function will return null also as a unique value.

In this article we have seen about the definition of NVL function and its syntax. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned. In our test table the values of COL3 and COL4 are equal in row 4, so we would only expect null returned for that row using the following query. 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. The following table will be used to demonstrate it. You can use the NVL function to return a value when a null occurs. Most aggregate functions ignore nulls. For example, consider a query that averages the five values , null, null, null, and Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null.

Oracle also considers two nulls to be equal if they appear in compound keys.