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,
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