All geek questions in one place

Computed column based on nullable columns

I want to create a calculated column that is a concatenation of several other columns. In the example below, fulladdress is null in the result set if any of the "real" columns is NULL. How do I set up a computed column function to allow for columns with a null value?

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 )

Thank you in advance

+1
source share
2 answers

This is pretty dirty, but here is the start:

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

(If isnull does not work, you can try coalesce . If none of them work, share what DMBS you are using.)

+4
source

You should not 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 build the complete address bar in your queries. By creating a field dynamically, you reduce redundancy in the table, and you have one less column to support (which will need to be updated with any changes to the column).

In your request you will do something like

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

The CONCAT() function performs the concatenation, and ISNULL() indicates your string if it is not null or the second parameter (which was passed as') if it is null

+1
source

Source: https://habr.com/ru/post/1389863/

More articles:


All Articles