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

ollowing 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

Read more: Connect SQL

Posted via email from .NET Info