Concatenation of NULL strings in SQL Server

Concatenation of String with a null value always yields a NULL result. When we concatenate two field values using ‘+’ operator in SQL Server, it yields a NULL result when either one of the value is NULL.

For example , SELECT FirstName + LastName AS Name yields NULL value where FirstName is ‘XX’ and LastName is Null.



But the actual requirement should be ‘XX’ instead of NULL.



This can be achieved in two ways:



1. Using SET CONCAT_NULL_YIELDS_NULL option

If The SET CONCAT_NULL_YIELDS_NULL option is OFF, concatenating a null value with a string yields the string itself , as the NULL value will be treated as an empty string



Example:

SET CONCAT_NULL_YIELDS_NULL OFF


GO


SELECT FirstName + LastName AS Name


GO


SET CONCAT_NULL_YIELDS_NULL ON


GO


SELECT FirstName + LastName AS Name


GO

Result:

XX


NULL



By default, CONCAT_NULL_YIELDS_NULL option is ON.

2) Using ISNULL Keyword



The ISNULL option replaces the NULL value with specified value. In this example if the Last Name is NULL value, then it will be replaced by empty string.

SELECT FirstName + ISNULL( LastName,’ ‘) AS Name

Result:

XX