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

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to NVL Function in SQL | NVL in SQL