NULL Value in SQL Server 2012

In this article I will explain the NULL value with IsNull() and Coalesce() functions in SQL Server.

In this article, I will explain how to use and handle a Null value in SQL Server. A NULL value represents an unknown value. It's unknown in the sense that the value is missing. If you are a developer, you will almost certainly have to deal with NULL values at some point in a project when you work with tables, Stored Procedures etc. So let's have a look at a practical example of how to use and handle a Null value in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

How to assign NULL values to variable

Now create a variable var and assign the NULL value to the variable.

DECLARE @Var int;

SET @Var = NULL;

SELECT @Var;

 

NULL values with Arithmetic Operation

SELECT 5+null as Result;

Output

NULL values with string Operation

SELECT 'Rohatash' + null as Result;

Output

NULL values with Datetime Operation

SELECT getdate()+null as Result;

Output

How to handle Null value in SQL Server

The IsNull() and Coalesce() functions are both used to replace null values with a user-defined value. The following defines how to handle a NULL value.

ISNULL() Function

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

Syntax

ISNULL (check_exp, change_value)

Example

SELECT ISNULl(5+null, 5) as Result;

Go

SELECT ISNULL('Rohatash' + null, 'Rohatash') as Result;

Go

SELECT ISNULL(getdate()+null, getdate()) as Result;

Output

Coalesce() Function

The Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.

Syntax

COALESCE ( expression [ ,...n ] ) 

Example

SELECT COALESCE(5+null, 5) as Result;

Go

SELECT COALESCE('Rohatash' + null, 'Rohatash') as Result;

Go

SELECT COALESCE(getdate()+null, getdate()) as Result;

Output