0
votes

Supprimer la virgule de fin de STUFF

Je travaille avec des adresses et je souhaite afficher les pays uniquement s'ils ne se trouvent pas au Royaume-Uni. Cela fonctionne mais il ne me reste qu'une virgule sur les adresses britanniques et je ne sais pas comment le supprimer. Je suppose que mon STUFF n'est pas la syntaxe correcte ou je dois le faire différemment?

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

Exemple de données:

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!

Requête:

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!

Sortie:

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

Sortie souhaitée:

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!')

sql-server tsql

1 commentaires

Notez que STUFF ne supprime pas la virgule de fin, il supprime la virgule de début.

3 Réponses :

2
votes

Cela n'a rien à voir avec votre STUFF, c'est votre ligne Country. Faites ceci et notez la position des ',' + .

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

Cheers Rob

0 commentaires

3
votes

    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
please check, this can help you

0 commentaires

2
votes

Réponse tardive

Je voulais juste souligner que CONCAT () traitera les valeurs NULL comme une chaîne vide.

Personnellement, je trouve cela un peu plus facile à lire.

Exemple

(No column name)
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!

Retours

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

0 commentaires