SQL SERVER: 3 Methods to Handle NULL for String Concatenation

If any of parameter/value used in string concatenation is found NULL, whole result becomes NULL.

DECLARE @FirstVal VARCHAR(10),

@SecondVal VARCHAR(10),

@ThirdVal VARCHAR(10),

@ForthVal VARCHAR(10)

SELECT @FirstVal ='First',@ThirdVal ='Third'

SELECT @FirstVal + @SecondVal + @ThirdVal

Following are three commonly used methods can solve this problem

1.  Using ISNULL()

It takes two parameters, original parameter for which you want to handle NULL and second one will be the alternate value or parameter which will replace NULL.

SELECT @FirstVal + ISNULL(@SecondVal,'') + @ThirdVal AS ConcatenationResult

2.  Using COALESCE()

COALESCE () is much more useful function as compared to ISNULL(). It is useful when you think that your alternate value for NULL can also be a NULL and you need to provide second alternate, third alternate and so on. COALESCE () returns the first nonnull expression among its arguments.

SELECT @FirstVal +  COALESCE(@SecondVal,@ForthVal,'') + @ThirdVal AS ConcatenationResult

3.  Setting CONCAT_NULL_YIELDS_NULL OFF

When you don’t want to use ISNULL() OR COALESCE() functions for each of your parameter in long statements to handle NULL values (as it is so lengthy process when handling  dozens of parameters for single batch or stored procedure), you can use set statement CONCAT_NULL_YIELDS_NULL to OFF. It will automatically replace every resultant NULL with empty string.

SET CONCAT_NULL_YIELDS_NULL OFF

      SELECT @FirstVal + @SecondVal + @ThirdVal AS ConcatenationResult

Keep in mind that Microsoft already issued a warning that in future versions of SQL SERVER, value for CONCAT_NULL_YIELDS_NULL will be fixed to ON. So applications will generate error if option is set to OFF. But still for SQL SERVER 2008 R2 and hopefully for DENALI you can use it.