CONCAT_NULL_YIELDS_NULL usage in SQL Server

By default, the CONCAT_NULL_YIELDS_NULL setting is ON at database/session/instance level.

CONCAT_NULL_YIELDS_NULL means that the concatenation of string with a NULL results NULL..

DECLARE @Str1 varchar(100) = NULL, @Str2 varchar(100) = 'Bandi'
SELECT @Str1 + ' ' + @Str2 -- NULL

The above code returns NULL as output due to the NULL value of @Str1....

If you want to ignore NULL values and do the concatenation, CONCAT_NULL_YIELDS_NULL property has to be OFF.

DECLARE @Str1 varchar(100) = NULL, @Str2 varchar(100) = 'Bandi'
SET CONCAT_NULL_YIELDS_NULL OFF 
SELECT @Str1 + ' ' + @Str2 -- Bandi

Result is Bandi