时间戳

首页 > 解决方案 > 从 STUFF 中删除尾随逗号

sql-server - 从 STUFF 中删除尾随逗号

问题描述

我正在处理地址，只想显示不在英国的国家/地区。这可行，但我只在英国地址上留下了一个尾随逗号，并且不知道如何删除它。我猜我STUFF的语法不正确，或者我需要以不同的方式执行此操作？

http://sqlfiddle.com/#!18/2cb48/5

示例数据：

CREATE TABLE [dbo].[AddressDetails]
(
    [AddressID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [PropertyNumber] [nvarchar](50) NULL,
    [PropertyName] [nvarchar](40) NULL,
    [Street] [nvarchar](40) NULL,
    [Locality] [nvarchar](40) NULL,
    [Town] [nvarchar](40) NULL,
    [PostCode] [nvarchar](15) NULL,
    [County] [nvarchar](40) NULL,
    [Country] [nvarchar](40) NULL,
)

INSERT INTO AddressDetails(PropertyNumber, PropertyName, Street, Locality, Town, PostCode, County, Country)
VALUES     
    ('1', NULL, 'Main Street', 'Ilford', 'London', 'E1 1JA', 'Londonshire', 'United Kingdom'),
    ('135', NULL, 'Apple Avenue', NULL, 'Reading', 'RG15 2FC', 'Berkshire', 'GB'),
    ('115/A', 'Cool Building', 'Siskin Close', 'Bushey', 'Watford', 'WD23 2HN', 'Greater London', 'GB'),
    ('1600', 'Amphitheatre', NULL, 'Parkway', 'Mountain View', '94043', 'California', 'USA'),
    ('7221', NULL, 'Shore Lane', 'Hempstead', 'NY', '11550', NULL, 'America!')

询问：

SELECT STUFF(                                           
    COALESCE(', '+NULLIF([PropertyName],''),'')
    +COALESCE(', '+NULLIF(PropertyNumber,''),'')
    +COALESCE(', '+NULLIF([Street],''),'')
    +COALESCE(', '+NULLIF([Locality],''),'')
    +COALESCE(', '+NULLIF([Town],''),'')                                            
    +COALESCE(', '+UPPER(NULLIF([Postcode],'')),'') 
    +COALESCE(', '+ CASE WHEN UPPER([Country]) IN ('GB', 'UNITED KINGDOM') THEN '' ELSE UPPER(NULLIF([Country],'')) END,'')                                                             
    ,1,2,'') as [FullAddress]
FROM AddressDetails

输出：

1, Main Street, Ilford, London, E1 1JA,
135, Apple Avenue, Reading, RG15 2FC,
Cool Building, 115/A, Siskin Close, Bushey, Watford, WD23 2HN,
Amphitheatre, 1600, Parkway, Mountain View, 94043, USA
7221, Shore Lane, Hempstead, NY, 11550, AMERICA!

期望的输出：

1, Main Street, Ilford, London, E1 1JA
135, Apple Avenue, Reading, RG15 2FC
Cool Building, 115/A, Siskin Close, Bushey, Watford, WD23 2HN
Amphitheatre, 1600, Parkway, Mountain View, 94043, USA
7221, Shore Lane, Hempstead, NY, 11550, AMERICA!

标签： sql-servertsql

解决方案

    Select right(rtrim(FullAddress),1) = ',' then substring(rtrim(FullAddress),1,len(rtrim(FullAddress))-1) as [FullAddress]  From 
(SELECT STUFF(                                           
    COALESCE(', '+NULLIF([PropertyName],''),'')
    +COALESCE(', '+NULLIF(PropertyNumber,''),'')
    +COALESCE(', '+NULLIF([Street],''),'')
    +COALESCE(', '+NULLIF([Locality],''),'')
    +COALESCE(', '+NULLIF([Town],''),'')                                            
    +COALESCE(', '+UPPER(NULLIF([Postcode],'')),'') 
    +COALESCE(', '+ CASE WHEN UPPER([Country]) IN ('GB', 'UNITED KINGDOM') THEN '' ELSE UPPER(NULLIF([Country],'')) END,'')                                                             
    ,1,2,'') as [FullAddress]
FROM AddressDetails) temp

请检查，这可以帮助你

推荐阅读