[dba-SQLServer] Append several fields into one

How about just concatenating them with spaces inside some nested replace
statements to remove the multiple spaces?

jwcolby wrote:
> 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?
>
>