NVL Function in SQL | NVL in SQL- sql - sql tutorial - learn sql
- To evaluate a specified expression and replace a non-NA value or empty string with one value and a NA value with another, use NVL2.
- The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value.
- It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.
- For example, if we have the following table,
Table price_Data
| Store_Name | price |
|---|---|
| Store A | 300 |
| Store B | NULL |
| Store C | 150 |
- The following SQL,
SELECT SUM (NVL(Sales,100)) FROM Sales_Data;
would generate result below:
| SUM (NVL(Sales,100)) |
|---|
| 550 |
- This is because NULL has been replaced by 100 via the NVL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.
Return Value
- The specified replacement value when the value of the base expression is NA, or the base expression when the value of the base expression is not NA.
- The data type of the return value is always the same as the data type of the base expression.
Syntax
NVL (exp , replacement-exp)
Arguments
expr
- The expression that you want to replace when it has a NA value or an empty string.
replacement-exp
- The value with which you want to replace a NA value.
- Example 8-45 Working with NVL
SHOW NVL('First String', 'Second String')
First String
SHOW NVL('', 'Second String')
Second String