Concatenation with Nulls in SQL

              Concatenation with Nulls in SQL Server 2008

In many Situtation you have a requirement to concatenate column but if any of column which you are concatenating contains NULL the result will be NULL

as Cocatenating any Value to Null will Result in NULL

In these type of scenario there are two solution

1 Using Coalesce Function

SELECT custid, country, region, city,   country + N',' + COALESCE(region, N'') + N',' + city AS location FROM Sales.Customers;

The Coalesce function will treat NULL as empty String

2 Setting a session option called CONCAT_NULL_YIELDS_NULL to OFF.

NOW SQL Server treats a NULL set to OFF as an empty string for concatenation purposes.

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT empid, firstname + N' ' + lastname AS fullname FROM HR.Employees;

Point to Remember : Once You have done with your result set Concat null yield back to on
 SET CONCAT_NULL_YIELDS_NULL ON as the Default values is ON

IN SQL SERVER 2012 You can achieve the Desired Result Directly with Concat Function

SET CONCAT_NULL_YIELDS_NULL ON;
SELECT CONCAT('abc', NULL) AS Test1;
output-abc
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT CONCAT('abc', NULL) AS Test2;

output-abc