SQL Server String Concatenation with Null

sql-server null string-concatenation calculated-columns
Alex picture Alex · May 26, 2010 · Viewed 147.8k times · Source

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?

Answer

marc_s picture marc_s · May 26, 2010

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.

Related questions

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must …

Read More
How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More