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,
- The following SQL,
would generate result below:
- 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.
- 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.
NVL (exp , replacement-exp)
- The expression that you want to replace when it has a NA value or an empty string.
- The value with which you want to replace a NA value.
- Example 8-45 Working with NVL