NVL
NVL is a function in SQL designed to replace NULL values with an alternative specified value. It's predominantly used within Oracle database systems to handle NULL data for calculations and data manipulation.
NVL(expr1, expr2);
- expr1: This is the value to test. It is the source column or expression that may contain a NULL.
- expr2: This is the substitute value. If expr1 is determined to be NULL, then NVL function will return expr2 instead.
Example
SELECT NVL(column_name, 'N/A') FROM table_name;Output
N/AExplanation
This code snippet represents an example of using NVL() function in Oracle SQL. If the value in column_name is null, it will return ‘N/A’. Otherwise, the actual value in the column will be returned.
ISNULL( check_expression , replacement_value )
- check_expression: This is the expression to be checked for NULL. It can be of any type.
- replacement_value: This is the value to return if the check_expression is NULL. The replacement value must be of a type that is implicitly convertible to the type of the check_expression.
Example
SELECT ISNULL(NULL, 'Default Value');Output
Default ValueExplanation
The NVL (or ISNULL in SQL Server) function is used to replace NULL values with a specified value. In the above example, the function checks the first argument (NULL) and since it is NULL, it returns the second argument (‘Default Value’).