sql-server - 你能优化这段代码吗？ T-SQL

Question

基本上我有三个字段，我正在创建一个新字段，这三个字段组合起来形成一个可邮寄地址；问题是某些字段包含空值，将 myString 添加到空值只会在 sql 中产生空值。

这是我的代码，有人可以使它更简洁吗？它看起来仍然很漂亮!

  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

您可以使用 isnull 和 ltrim 删除任何前导空格:

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