SQL Server String Concatenation with Null

From SQL Server 2012 this is all much easier with the CONCAT function.

It treats NULL as empty string

DECLARE @Column1 VARCHAR(50) = 'Foo',
        @Column2 VARCHAR(50) = NULL,
        @Column3 VARCHAR(50) = 'Bar';


SELECT CONCAT(@Column1,@Column2,@Column3); /*Returns FooBar*/

You can use ISNULL(....)

SET @Concatenated = ISNULL(@Column1, '') + ISNULL(@Column2, '')

If the value of the column/expression is indeed NULL, then the second value specified (here: empty string) will be used instead.


Use COALESCE. Instead of your_column use COALESCE(your_column, ''). This will return the empty string instead of NULL.