CODEWITHSUNDEEP
sqlsql-server
Rob Packwood
Rob Packwood

Reputation: 3798

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 rtrim(coalesce(Country,'') ) != '' then rtrim(Country ) + @NL else '' end
as FormattedMailingAddress    
from Address 
where Id = 1

Upvotes: 1

Views: 3183

Answers (2)

Dave Mason
Dave Mason

Reputation: 4936

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

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166406

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

Upvotes: 3

Related Questions