[dba-SQLServer] Append several fields into one

I've done it, but not for that many records.  I'm not sure how fast it
would be.
What I did was use a series IF statements in a view and then used that
view to update the new record.
For example....
Field names for the below could be:  
HouseNumber; HouseDirection, StreetName, StreetIdentifier;
CityDirection; UnitIdentifier; UnitNumber

And then to shorten them for ease of the example:  HN, HD, SN, SI, CD,
UI, UN

IIF(IsNull(HN),"", HN &" ") & IIF(IsNull(HD),"", HD &" ") &
IIF(IsNull(SN),"", SN &" ") & _ IIF(IsNull(SI),"", SI &" ") &
IIF(IsNull(CD),"", CD &" ") & IIF(IsNull(UI),"", UI &" ") & _
IIF(IsNull(UN),"", UN &" ")

Instead of IsNull a >" " statement could be used or something similar.

HTH

Jim


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, June 23, 2009 8:53 AM
To: Dba-Sqlserver
Subject: [dba-SQLServer] Append several fields into one

I am trying to append all of the pieces of an address into a single
address string.  As an example, 
if I have the address:

121 S. Main Street NE Apt #201

this address will be stored with standardized spellings of all the parts
into a set of fields:

121
S
Main
St
NE
Apt
201

I need to get this all back into a single field.  Understand however
that lots of those pieces might 
be missing in any given address.  For example the next field might be:

121

Main
St

Ste
1342

So... I need to get all the pieces back into a single address field.  If
a piece exists then it 
needs padding around it so I don't end up with:

121MainStSte1342

But if a piece does not exist then I do not want the padding for that
missing field.

I am assuming that I will be using a stored procedure.  Pass in the
database, table, 
UnifiedAddressField, then all of the field parts.

It seems clumsy to do it this way, not to mention record by record, for
50 million records... well...

There must be a better way.  Has anyone on the list done this before?

-- 
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

################################################################################
If you have received this message in error, please contact the sender
immediately and be aware that the use, copying, or dissemination of 
this information is prohibited. This email transmission contains 
information from NCI Information Systems, Inc. that may be considered 
privileged or confidential and is intended solely for the named 
recipient.
################################################################################