MS SQL Server 9 - How to returns the first non-NULL value from a series of expressions?

 Hello All,

In this article we will discuss how to return the first non-null value from a series of expressions.

Solution for this is SQL Server COALESCE() Function.

The COALESCE and ISNULL MS SQL Server T-SQL functions are used to return the first nonnull expression among the input arguments.

Comparing COALESCE and ISNULL

• The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
• The Major first difference, coalesce is defined by the SQL standard and will work on nearly all DBMS. isnull() only works with Microsoft products.
• ISNULL takes two parameters, COALESCE can take n parameters, it depends on the requirement and version of SQL Server as to which is preferred.

Lets Learn This Through Query.

• Open SQL Server Management Studio          
• Create New Database

Create Database BestOfDotNet_Practice

• Use The Created Database

Use BestOfDotNet_Practice

• Create New Table

Create Table Tbl_Employees 
(

     Id Int,

     FirstName Nvarchar(50),
     LastName Nvarchar(50),
     PetName Nvarchar(50)

)

• Insert Three Records

Insert Into Tbl_Employees(Id,FirstName,LastName,PetName) 
values (1,'TestFirstName',null,null)
Insert Into Tbl_Employees(Id,FirstName,LastName,PetName) 
values (2,null,'TestLastName',null)  
Insert Into Tbl_Employees(Id,FirstName,LastName,PetName) 
values (3,null,null,'TestPetName')

• Select All Records

Select * From Tbl_Employees

• Select Only Names without Null Values. If We Use IsNull it takes only two parameters, but third value will be return as Null

Select ISNULL(FirstName,LastName) As Name From Tbl_Employees

• If We Add Another Parameter to ISNULL, error message will be displayed

Select ISNULL(FirstName,LastName, PetName)As Name from Tbl_Employees

• But the solution is COALESCE

Select COALESCE(FirstName,LastName, PetName)As Name from Tbl_Employee

That's It...

Please Add your comment to help us... 👇😊