[dba-SQLServer] Put humpty dumpty back together again

Or you could try something like:

SELECT CASE WHEN HouseNumberPrefix IS NULL THEN '' ELSE HouseNumberPrefix +
' ' END +
CASE WHEN HouseNumber IS NULL THEN '' ELSE HouseNumber + ' ' END +
CASE WHEN HouseNumberSuffix IS NULL THEN '' ELSE HouseNumberSuffix + ' ' END
Etc Etc AS MainAddress

This should give you an address line without additional spaces....

Paul Hartland

2008/6/8 Bobby Heid <bheid at sc.rr.com>:

> 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 <http://www.colbyconsulting.com/>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com