[dba-SQLServer] Put humpty dumpty back together again

John,

I think you want something along the lines of:

Isnull(HouseNumberPrefix,'') + ' ' + Isnull(HouseNumber,'') + ' ' + ...

Of course, you'd have to figure out what to do with the empty spaces for the
empty fields.

Bobby

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, June 06, 2008 10:20 AM
To: Access Developers discussion and problem solving; Dba-Sqlserver
Subject: [dba-SQLServer] Put humpty dumpty back together again

I have a database that has split the address line into

HouseNumberPrefix
HouseNumber
HouseNumberSuffix
Direction
StreetName
Mode (N, NW etc)
Quadrant
Appt#

I need to put Humpty back together again to feed off to 
Address Validation.  How would I do that in SQL?

I THINK I can just append them all together with spaces 
between the parts and that would be fine EXCEPT that when 
you do something like NULL + SomeString you end up with null.

How would I do what I am trying to do in SQL?

-- 
John W. Colby
www.ColbyConsulting.com