Eliminating Nulls from an address

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)

Eliminating Nulls from an address

Author

Topic

darinh
Yak Posting Veteran

58 Posts

Posted - 2005-06-14 : 01:07:12

I have a table of Customer's Addresses like this

CREATE TABLE [dbo].[CustomerAddress] (
[CustomerID] [int] NOT NULL ,
[Address1] [varchar] (30) NOT NULL ,
[Address2] [varchar] (30) NULL ,
[Address3] [varchar] (30) NULL ,
[Suburb] [varchar] (30) NULL ,
[City] [varchar] (30) NOT NULL
) ON [PRIMARY]

I always have a CustomerID, Address1 and City, but the other lines are all optional depending on the size of the town which could be from 20 people to a million.

e,g

INSERT INTO dbo.CustomerAddress VALUES(501234, '1 First Street', Null, Null, 'Takapuna', 'North Shore')
INSERT INTO dbo.CustomerAddress VALUES(401236, 'Level 3, Big Bank Building', '40 High Street', Null, 'Penrose', 'Auckland')
INSERT INTO dbo.CustomerAddress VALUES(611237, '1 Main Road', Null, Null, Null, 'Temuka')

For display purposes, I need to write a query that returns a distinct list with the null values at the end rather than in the middle

This doesn't work, but hopefully illustrates roughly what I want to do.

SELECT
Address1,
COALESCE(Address2, Address3, Suburb, City) AS Address2,
COALESCE(Address3, Suburb, City) AS Address3,
COALESCE(Suburb, City) AS Suburb,
City,
FROM
dbo.CustomerAddress

I would like to return this

'1 First Street', 'Takapuna', 'North Shore', Null, Null
'Level 3, Big Bank Building', '40 High Street', 'Penrose', 'Auckland', Null
'1 Main Road', 'Temuka', Null, Null, Null

but can't figure out an easy way to do this

Thanks for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-14 : 01:49:51

It is better to do this in your presentation layer

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-14 : 07:39:07

Yep. Leave that to the client to handle. SQL should give the client data, not make it look pretty.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources