SQL Server Conditional Mailing Address Formatting

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 rt开发者_高级运维rim(coalesce(Country,'') ) != '' then rtrim(Country ) + @NL else '' end
as FormattedMailingAddress    
from Address 
where Id = 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

I realize this is an old question, but there is a new solution to this problem: the CONCAT_WS() function, which is new for SQL Server 2017 (it's also available for Azure SQL Database).

SELECT CONCAT_WS (
    CHAR(13) + CHAR(10),    --Separator
    NULLIF(AttentionLine, ''),
    NULLIF(Recipient, ''),
    NULLIF(AddlAddrLine, ''),
    NULLIF(DeliveryAddr, ''),
    NULLIF(LastLine, ''),
    NULLIF(Country, '')
)
AS FormattedMailingAddress    
FROM Address 
WHERE Id = 1

NULL values are ignored by the function, which is why NULLIF is used with each argument/parameter in this example. (When the argument/parameter evaluates to NULL, the separator won't be added either). Here's a short blog post with some more details: New For SQL Server 2017: T-SQL Function CONCAT_WS