代码之家 › 专栏 › 技术社区 › Rob Packwood

SQL Server条件邮件地址格式

sql-server sql

Rob Packwood · 技术社区 · 16 年前

我有下面的SQL将一个美国地址格式化成每一行的一个邮寄地址,但是它相当难看。有没有更好的办法来解决这个问题,还是一定要这么难看?另外,这个代码的问题是,它总是以一个额外的新行结束。

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

2 回复 | 直到 16 年前

Dave Mason 8 年前

我意识到这是一个老问题,但有一个新的解决办法,这个问题:解决 CONCAT_WS() 函数,这是SQL Server 2017的新功能(也可用于Azure SQL数据库)。

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 值被函数忽略,这就是为什么 NULLIF 与本例中的每个参数一起使用(当参数/参数的计算结果为 无效的 ,也不会添加分隔符)。以下是一篇简短的博客文章,其中包含了更多细节: New For SQL Server 2017: T-SQL Function CONCAT_WS

Adriaan Stander 16 年前

如果Sql Server设置为NULL+varchar返回NULL( SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) ),这会有所帮助。

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