Computed column based on nullable columns

问题:

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
)

Thanks in advance

回答1:

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

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

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

回答2:

You shouldn't have a full address column (which is a duplicate of other columns) stored in your database unless you have a good reason. The correct way would be to construct the full address string in your queries. By creating the field dynamically you reduce redundancy in the table and you have one less column to maintain (which would need to be updated anytime any other column changes).

In your query you would do something like

SELECT CONCAT(ISNULL(address,''), ISNULL(address2,''), ISNULL(city,''), ISNULL(state,''), ISNULL(zip,'')) AS fulladdress FROM Locations; 

The CONCAT() function performs concatenation and the ISNULL() gives you your string if it's not null or the second param (which was passed as '') if it is null