Concatenating Null values with a String or Number in SQL 2008

Concatenating Null values with a String or Number in SQL 2008 and 2012

Anything concatenate with NULL yields to output as NULL but in case when you want to concatenate NULL field with other but resulting NULL can hamper your output.

Take an example like you have a table where you have EmployeeFirst Name and EmployeelastName columns in the report and you want to show EmployeeFull Name as seprate column in the report

but some of employee dont have last Name so Concatenating Employee first and last will result employeefull name as null for employee who doesnt have last Name

below is the sample table with above example

create table Employee_NUll

(Empid int ,EmpFName nvarchar(50),EmpLName nvarchar(50))

Insert into Employee_NUll values(1,'Vijay','Rana')

,(2,'Amit',null),(3,'Suresh',null),(4,'Ravi','Rai')

select (EmpFName+EmpLName)EmpFullName,* from Employee_NUll

Output

EmpFullName Empid EmpFName EmpLName

VijayRana    1 Vijay Rana

NULL         2 Amit  NULL

NULL        3     Suresh NULL

RaviRai       4   Ravi Rai

If you see output the Employee full Name is coming NULL for employee having last name NULL So In order to handles this 

SET CONCAT_NULL_YIELDS_NULL Off;

select (EmpFName+EmpLName)EmpFullName,* from Employee_NUll

Output

EmpFullName Empid EmpFName EmpLName

VijayRana    1 Vijay Rana

Amit         2 Amit  NULL

Suresh        3     Suresh NULL

RaviRai       4   Ravi Rai

Other Methods to concatenate with NULL values

IsNUll function

select isnull(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

Coalesce Function

select coalesce(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

SQL 2012 has provided a new function to concat with NULL values

--This will work only in SQL 2012 and above.....

select concat(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll