Can you optimize this code? T-sql

Question

Essentially, I have three fields, and I create a new one, which consists of three combined ones that create the mailing address; the problem is that some fields contain null values ​​and adding myString to null results in a null value in sql.

So this is my code, can anyone make it cleaner? He still looks pretty.

UPDATE [mydb].[dbo].[Account] SET [Billing Street] = CASE WHEN [(Billing Address 1)] is null and [(Billing Address 2)] is null THEN [(Billing Address 3)] WHEN [(Billing Address 1)] is null and [(Billing Address 3)] is null THEN [(Billing Address 2)] WHEN [(Billing Address 2)] is null and [(Billing Address 3)] is null THEN [(Billing Address 1)] WHEN [(Billing Address 1)] is null THEN [(Billing Address 2)] + ' ' + [(Billing Address 3)] WHEN [(Billing Address 2)] is null THEN [(Billing Address 1)] + ' ' + [(Billing Address 3)] WHEN [(Billing Address 3)] is null THEN [(Billing Address 1)] + ' ' + [(Billing Address 2)] ELSE [(Billing Address 1)] + ' ' + [(Billing Address 2)] + ' ' + [(Billing Address 3)] END 

Answer 1

You can use isnull and ltrim to remove all leading spaces:

 update [mydb].[dbo].[Account] set [Billing Street] = ltrim(isnull([(Billing Address 1)], '') + isnull(' ' + [(Billing Address 2)], '') + isnull(' ' + [(Billing Address 3)], '')) 

Answer 2

If both old and new columns coexist, you better create a computed column - this way they will never “synchronize” with each other.

Take the Andomars instruction and modify it as follows:

 ALTER TABLE Account ADD [Billing Street] AS LTRIM... 

Where LTRIM ... continues, as in Andomar answer