CODEWITHSUNDEEP
sql-server
Matt
Matt

Reputation: 4117

Can you optimize this code? T-SQL

Essentially I have three fields and I am creating a new one which is the three combined making a mailable address; problem being some fields contain null values and adding myString to a null just produces a null in sql.

So this is my code, can anyone make it any cleaner? It's still looking pretty butch!

  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

Upvotes: 4

Views: 113

Answers (2)

Andomar
Andomar

Reputation: 238098

You could use isnull and ltrim to remove any leading whitespace:

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

Upvotes: 7

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

If both old and new columns are going to coexist, you'd be better creating a computed column - that way, they never get "out of sync" with each other.

Take Andomars statement, and change it to read:

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

Where LTRIM... continues as in Andomar's answer

Upvotes: 2

Related Questions