SQL Server Conditional Mailing Address Formatting

Question

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? Also, the problem with this code is that it always ends up with an extra new line at the end.

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

Answer 1

If your Sql Server Settings are such that NULL + varchar returns NULL (SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)), this can help.

DECLARE @Address TABLE(
        ID INT,
        AttentionLine VARCHAR(50),
        Recipient VARCHAR(50),
        AddlAddrLine VARCHAR(50),
        DeliveryAddr VARCHAR(50),
        LastLine VARCHAR(50),
        Country VARCHAR(50)
)

declare @NL varchar(2); 
set @NL = char(13) + char(10); 

INSERT INTO @Address SELECT 1, NULL, '1', NULL, '2', NULL, '3'

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     ,
    RTRIM(coalesce(AttentionLine + @NL,'')) + 
    RTRIM(coalesce(Recipient + @NL,'')) + 
    RTRIM(coalesce(AddlAddrLine + @NL,'')) + 
    RTRIM(coalesce(DeliveryAddr + @NL,'')) + 
    RTRIM(coalesce(LastLine + @NL,'')) + 
    RTRIM(coalesce(Country + @NL,'')) 
from @Address  
where Id = 1