SQL Server Conditional Mailing Address Formatting
Posted
by Rob Packwood
on Stack Overflow
See other posts from Stack Overflow
or by Rob Packwood
Published on 2010-03-17T17:59:34Z
Indexed on
2010/03/17
18:01 UTC
Read the original article
Hit count: 272
Filed under:
sql
|sql-server
I have the following SQL to format a US address into each line for a mailing address but it is rather ugly. Is there a better way to solve this problem or does it have to be this ugly?
declare @NL varchar(2);
set @NL = char(13) + char(10);
select
case when rtrim(coalesce(AttentionLine,'') ) != '' then rtrim(AttentionLine ) + @NL else '' end
+ case when rtrim(coalesce(Recipient,'') ) != '' then rtrim(Recipient ) + @NL else '' end
+ case when rtrim(coalesce(AddlAddrLine,'') ) != '' then rtrim(AddlAddrLine ) + @NL else '' end
+ case when rtrim(coalesce(DeliveryAddr,'') ) != '' then rtrim(DeliveryAddr ) + @NL else '' end
+ case when rtrim(coalesce(LastLine,'') ) != '' then rtrim(LastLine ) + @NL else '' end
+ case when rtrim(coalesce(Country,'') ) != '' then rtrim(Country ) + @NL else '' end
as FormattedMailingAddress
from Address
where Id = 1
© Stack Overflow or respective owner
