Search code examples
sql-servernullstring-concatenationcalculated-columns

SQL Server String Concatenation with Null

I am creating a computed column across fields of which some are potentially null.

The problem is that if any of those fields is null, the entire computed column will be null. I understand from the Microsoft documentation that this is expected and can be turned off via the setting SET CONCAT_NULL_YIELDS_NULL. However, there I don't want to change this default behavior because I don't know its implications on other parts of SQL Server.

Is there a way for me to just check if a column is null and only append its contents within the computed column formula if its not null?

Solution

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