[dba-SQLServer] Put humpty dumpty back together again

Yes, that is what I want.

I don't think the extra spaces matter for my purposes, 
though I am not entirely sure either.  The entire point of 
the exercise is to get a single address line to hand off to 
another program to do address validation on.  How will that 
program handle extra spaces in there?  I have no clue.

The bigger problem is that I have to do 97 million 
addresses, and have 7 pieces to append which means close to 
700 million calls to isnull().

I am trying very hard not to "pre-process" this simply 
because I get weekly updates to this database and any 
preprocessing I do to the main, I have to do the updates.  I 
would simply update nulls to '' for all the fields (which I 
did to another db) but that preprocessing would then have to 
be done to every update.

John W. Colby
www.ColbyConsulting.com


Bobby Heid wrote:
> 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?
>