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?

"Coalesce" is the ANSI-standard function name, but ISNULL is easier to spell.
– Philip KelleyMay 26 '10 at 21:08

1

And ISNULL seems to be a tad faster on SQL Server, too - so if you want to use it in a function that concatenates strings into a computed column, you might forgo the ANSI standard and opt for speed (see Adam Machanic: sqlblog.com/blogs/adam_machanic/archive/2006/07/12/…)
– marc_sMay 26 '10 at 21:15

Just used this Isnull(,) query, it heped a lot as I was concatenating values together and if one of them was null everything became null too.
– SizonsFeb 3 '16 at 10:33

You can also use CASE - my code below checks for both null values and empty strings, and adds a seperator only if there is a value to follow:

SELECT OrganisationName,
'Address' =
CASE WHEN Addr1 IS NULL OR Addr1 = '' THEN '' ELSE Addr1 END +
CASE WHEN Addr2 IS NULL OR Addr2 = '' THEN '' ELSE ', ' + Addr2 END +
CASE WHEN Addr3 IS NULL OR Addr3 = '' THEN '' ELSE ', ' + Addr3 END +
CASE WHEN County IS NULL OR County = '' THEN '' ELSE ', ' + County END
FROM Organisations

There's quite a bit of redundant nested bracketing there that could be removed. Another tip is that you could also remove the case statement as if address1 is null the whole expression will evaluate to null (though having the case statement does draw attention that this can happen)
– AlternatorFeb 24 '14 at 23:03