基于可为空的列的计算列 [英] Computed column based on nullable columns

问题描述

我想创建一个计算列，该计算列是其他几个列的串联。在以下示例中，当真实列中的任何一个为空时，fulladdress在结果集中为空。如何调整计算列功能以考虑可为空的列？

I want to create a computed column that is the concatenation of several other columns. In the below example, fulladdress is null in the result set when any of the 'real' columns is null. How can I adjust the computed column function to take into account the nullable columns?

CREATE TABLE Locations
(
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [fulladdress]  AS (((([address]+[address2])+[city])+[state])+[zip]),
    [address] [varchar](50) NULL,
    [address2] [varchar](50) NULL,
    [city] [varchar](50) NULL,
    [state] [varchar](50) NULL,
    [zip] [varchar](50) NULL
)

预先感谢

推荐答案

这很快就很混乱，但这是一个开始：

This gets messy pretty quick, but here's a start:

ISNULL(address,'')      + ' ' 
  + ISNULL(address2,'') + ' '
  + ISNULL(city,'')     + ' ' 
  + ISNULL(state,'')    + ' '
  + ISNULL(zip,'')

（如果 isnull 不起作用，则可以尝试 coalesce 。如果两种方法都不起作用，请分享您使用的DMBS。）

(If isnull doesn't work, you can try coalesce. If neither work, share what DMBS you're using.)

这篇关于基于可为空的列的计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！