NVL Function - Replace NULL - Oracle to SQL Server Migration

In Oracle, NVL(exp1, exp2) function accepts 2 expressions (parameters), and returns the first expression if it is not NULL, otherwise NVL returns the second expression.

In SQL Server, you can use ISNULL(exp1, exp2) function.

Oracle Example:

  -- Return 'N/A' if name is NULL
  SELECT NVL(name, 'N/A') FROM countries;

SQL Server Example:

  -- Return 'N/A' if name is NULL
  SELECT ISNULL(name, 'N/A') FROM countries;

Oracle NULL to SQL Server conversion summary:

	Oracle	SQL Server
Syntax	NULL(exp1, exp2)	ISNULL(exp1, exp2)
Alternatives	CASE	CASE	ANSI SQL Compliant
	COALESCE	COALESCE
	NVL2	IIF	Since SQL Server 2012

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

In Oracle, NVL function is used to replace NULL with the specified value, and can be converted to ISNULL function in SQL Server.

Note that if you want your application to support both Oracle and SQL Server databases, you can use ANSI SQL compliant CASE expression or COALESCE function that are supported by both Oracle and SQL Server:

Oracle:

  -- Return 'N/A' if name is NULL
  SELECT NVL(name, 'N/A') FROM countries;
 
  -- The same result using CASE expression
  SELECT CASE WHEN name IS NULL THEN 'N/A' ELSE name END FROM countries;
 
  -- Using COALESCE
  SELECT COALESCE(name, 'N/A') FROM countries;

SQL Server:

  -- Return 'N/A' if name is NULL
  SELECT ISNULL(name, 'N/A') FROM countries;
 
  -- The same result using CASE expression
  SELECT CASE WHEN name IS NULL THEN 'N/A' ELSE name END FROM countries;
 
  -- Using COALESCE
  SELECT COALESCE(name, 'N/A') FROM countries;

Additionally, you can use Oracle NVL2 and SQL Server IIF function (available since SQL Server 2012 ):

Oracle:

  -- Return 'N/A' if name is NULL
  SELECT NVL2(name, name, 'N/A') FROM countries;

SQL Server 2012 or later:

  -- Return 'N/A' if name is NULL
  SELECT IIF(name IS NULL, 'N/A', name) FROM countries;

• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• NVL Function
• NVL2 Function
• CASE Expression
• COALESCE Function

Microsoft SQL Server 2012 - Books Online

• ISNULL Function
• COALESCE Function
• CASE Expression
• IIF Function

SQLines offers services to convert databases and applications from Oracle to Microsoft SQL Server. For more information, please Contact Us.