SQL Server 2005: ISNULL() function

In MS SQL Server, when a string is concatenated with other string variable whose values is NULL, the result will give NULL. Use ISNULL() function to solve this type of problem.


In MS SQL Server, when a string is concatenated with other string variable whose values is NULL, the result is NULL. Consider two strings s1=’Hello’ & s2=NULL. The resultant string s3, where s3 = s1 + s2, will give NULL. Means, whenever you will try to concatenate two columns where either column has value NULL, the result column will show NULL.
I searched lots & the better result I found after some RnD is to use the function ‘ISNULL()’. In the given below example, I have taken three string variables @s1, @s2 & @s3. Variable @s1 initialized with ‘Hello’ while @s2 initialized with NULL. After concatenation @s1 & @s2 assigned to @s3 and you can see the result displayed is NULL.
declare @s1 varchar(10), @s2 varchar(10), @s3 varchar(20)
set @s1 = ('Hello')
set @s2 = (NULL)
set @s3 = (@s1 +' '+ @s2)
print (@s3)

To solve this problem, you can use either ISNULL () or COALESCE() function. So, to concatenate a string value with null value to get exact result, we can modify our above example as:

declare @s1 varchar(10), @s2 varchar(10), @s3 varchar(20)
set @s1 = ('Hello')
set @s2 = (NULL)
set @s3 = (@s1 +' '+ ISNULL(@s2, ''))
print (@s3)

This will return actual result even when you will concatenate a string with null value. The function ‘ISNULL()’ takes two arguments, Ist column name/variable which contains NULL value and other argument is the value that you want to replace with. Any character/string you can use to manipulate it according to your choice.

Your comments/suggestion will be highly appreciated.