Pulling together an address - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Pulling together an address

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-02-08 : 05:09:04

Hi I have various fields for an address. i.e. Address1, address2, address3, city, state and zip.
I'd like to pull them together in a query as "Billing Adress"

e.g. 123 EasyStreet, Green Buildings, London NW7 6UP

from

Address1: 123 EasyStreet
Address2: Green buildings
Address3:
City: London
State:
Zip: NW6 7UP

Just not quite sure how to suppress blanks or nulls

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 05:12:51

use COALESCE or ISNULL

something like

SELECT COALESCE(Address1 + ',','') + COALESCE(Address2 + ',','') + COALESCE(Address3 + ',','') + COALESCE(City + ',','') + COALESCE(State + ',','') + COALESCE(Zip,'') AS BillingAddress FROM Table