>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.
@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
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.
      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.

Posted on January 18, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: