CONCAT_NULL_YIELDS_NULL usage in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 248
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

Comments or Responses

Login to post response