How often do we think of NULL values while concatenating NULLs with string values? String concentration with NULL values can give us some unexpected results if we are not aware of CONCAT_NULL_YIELDS_NULL database SET options.
CONCAT_NULL_YIELDS_NULL options control whether concatenation results shall be treated as NULL or empty string values.
An Important Note from SQL Server Books Online
In a future version of SQL Server, CONCAT_NULL_YIELDS_NULL will always be ON, and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Syntax
SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
Description
SET CONCAT_NULL_YIELDS_NULL ON
When the CONCAT_NULL_YIELDS_NULL database option in ON, concatenating a string value with NULL will result in NULL as an output. When we have CONCAT_NULL_YIELDS_NULL as ON, then NULL values are considered as UNKNOWN, hence concatenating a string value with UNKNOWN yields in UNKNOWN, NULL as output.
Example:
SET CONCAT_NULL_YIELDS_NULL ON; SELECT 'tech-recipes' + NULL; --results in NULL.
SET CONCAT_NULL_YIELDS_NULL OFF
When the CONCAT_NULL_YIELDS_NULL database option in OFF, concatenating a string value with NULL will return a string as output. When we have CONCAT_NULL_YIELDS_NULL as OFF, NULL values are considered as an empty string, hence concatenating a string value with empty string yields in a string as output because it is considered as TRUE instead of UNKNOWN.
Example:
SET CONCAT_NULL_YIELDS_NULL OFF; SELECT 'tech-recipes' + NULL; --results in 'tech-recipes'.
In future versions of SQL Server, the CONCAT_NULL_YIELDS_NULL set option will be deprecated. Therefore, we should avoid using it in future development work. We can use the ISNULL or COALESCE function to handle the behavior of NULL values while concatenating strings. It is also advisable to use CONCAT function to concat strings introduced with SQL Server 2012 edition which handles NULL values concatenation perfectly.
Use the CONCAT / ISNULL/ COALESCE Function Instead of CONCAT_NULL_YIELDS_NULL
The following examples must be followed while writing code instead of using CONCAT_NULL_YIELDS_NULL database options while concatenating strings.
DECLARE @MyVar1 as VARCHAR( 50) = 'You are ' , @MyVar2 as VARCHAR (50) = NULL, @MyVar3 as VARCHAR (50) = 'on tech-recipes.com'; SELECT CONCAT (@MyVar1, @MyVar2, @MyVar3 );
In the example above, we are using the CONCAT function introduced with SQL Server 2012. It will treat NULL in @MyVar2 variable as an empty string while concatenating strings. It is always better to use this function instead of playing with the CONCAT_NULL_YIELDS_NULL set option to avoid any code changes as this setting is going to be deprecated.
DECLARE @MyVar1 as VARCHAR( 50) = 'You are ', @MyVar2 as VARCHAR (50) = NULL, @MyVar3 as VARCHAR (50) = 'on tech-recipes.com'; SELECT ISNULL(@MyVar1,'') + ISNULL(@MyVar2,'') + ISNULL(@MyVar3,'');
In the example above, we are using the ISNULL function to check and replace any NULL occurrence with an empty string instead of using the CONCAT_NULL_YIELDS_NULL setting. We can use the ANSI function COALESCE as well instead of ISNULL.
