Getting NULL values instead of data while concatenation – SQL Server

I have to concatenate first name with last name. I got too many NULL values which it should not. Then I found the reason behind it.

Data in TblNames :

FIRST_NAME  LAST_NAME
Edward            Jones
Jason               NULL
John                 Smith
Robert              NULL      

4 row(s) affected.

Expected Result:

NAME
Edward Jones
Jason
John Smith
Robert

4 row(s) affected.

Before finding the solution:

SELECT FIRST_NAME + ‘ ‘ + LAST_NAME AS [NAME] FROM TBLNAMES;

NAME
Edward Jones
NULL
John Smith
NULL

5 row(s) affected.

I realized the if any of the columns(FIRST_NAME or LAST_NAME) is NULL, it returns the NULL value. To resolve this, set the concat_null_yields_null to OFF.

Solution:

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT FIRST_NAME + ‘ ‘ + LAST_NAME AS [NAME] FROM TBLNAMES;
SET CONCAT_NULL_YIELDS_NULL ON;