[dba-SQLServer] Append several fields into one

John, you can do it right out of a select, something like this

IsNull(StreetNumber + ' ', '') + IsNull(Direction + ' ', '') +
IsNull(StreetName + ' ', '') etc...

I added the space padding right inside of the IsNull formula because this
way you get a padding, for each field, unless there is nothing then the
result is no padding.  In Sql Server Null + a value = Null :)

If you don't want to accidentally end up with a space padding on the right
simply trim your statement with RTRIM(your isnull series)



-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Tue, Jun 23, 2009 at 6:53 AM, jwcolby <jwcolby at colbyconsulting.com>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?
>
> --
> 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
>
>