CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlstring-concatenation
Coldchain9
Coldchain9

Reputation: 1745

How to remove leading space using FOR XML PATH in SQL Server

I am trying to string aggregate some data and am having an issue. This data will be used for a mail merge.

After I aggregate my data into a single column and then go to copy it into an email via mail merge the result looks like

Owner: Me, Pet: Dog, Other Pet: Cat, Pet2: Dog2, OtherPet2: Cat2
 Owner: Me, Pet: Dog, Other Pet: Cat, Pet2: Dog2, OtherPet2: Cat2
 Owner: Me, Pet: Dog, Other Pet: Cat, Pet2: Dog2, OtherPet2: Cat2
 Owner: Me, Pet: Dog, Other Pet: Cat, Pet2: Dog2, OtherPet2: Cat2

I want each line to have the same indentation but I cannot currently achieve this trying a number of things.

My reproducible code is here:

DROP TABLE #Temp1
DROP TABLE #temp2
CREATE TABLE #Temp1 (Owner varchar(10), val1 varchar(10), val2 varchar(10), val3 varchar(10), val4 varchar(10))

INSERT INTO #Temp1 values('Me','Dog','Cat', 'Dog2', 'Cat2')
INSERT INTO #Temp1 values('You','Dog','Cat', 'Dog2', 'Cat2')
INSERT INTO #Temp1 values('He','Dog','Cat', 'Dog2', 'Cat2')
INSERT INTO #Temp1 values('She','Dog','Cat', 'Dog2', 'Cat2')
INSERT INTO #Temp1 values('Me','Dog','Cat', 'Dog2', 'Cat2')
INSERT INTO #Temp1 values('Me','Dog','Cat', 'Dog2', 'Cat2')
INSERT INTO #Temp1 values('Me','Dog','Cat', 'Dog2', 'Cat2')



SELECT Owner, 
Pets = 
cast(STUFF 
( 
( 
SELECT  (' '+ 'Owner: ' + Owner +  ', Pet: ' + val1 + ', Other Pet: ' + val2 + ', Pet2: ' + cast(val3 as varchar)+ ', OtherPet2: ' + RTRIM(val4) + char(13))
FROM #Temp1  as b 
WHERE b.Owner=a.Owner 
FOR XML PATH(''),TYPE 
).value('.', 'varchar(1000)'), 1, 1, '') as text) 
INTO #Temp2

FROM #Temp1 as a 

GROUP BY Owner
Order by Owner 


SELECT * FROM #Temp2

The 2nd row for Owner "Me" would be the entry you can copy and paste to troubleshoot the indentation. What would I need to add to strip that extra space added at the beginning of my string concat?

Thank you

Upvotes: 2

Views: 3357

Answers (1)

Thom A
Thom A

Reputation: 95989

This problem is your placement of CHAR(13). You want that at the start instead of the space:

SELECT Owner,
       CAST(STUFF((SELECT (CHAR(13) + 'Owner: ' + Owner + ', Pet: ' + val1 + ', Other Pet: ' + val2 + ', Pet2: ' + CAST(val3 AS varchar) + ', OtherPet2: ' + RTRIM(val4) )
                   FROM #Temp1 AS b
                   WHERE b.Owner = a.Owner
                  FOR XML PATH(''), TYPE).value('.', 'varchar(1000)'),1,1,'') AS text) AS Pets
INTO #Temp2
FROM #Temp1 AS a
GROUP BY Owner
ORDER BY Owner;

STUFF is only removing the first character of the entirety of the XML, not the first character of every delimited item created within the XML. CHAR(13) is your delimiter here, not a whitespace (' '), so put CHAR(13) at the start instead.

When using the FOR XML PATH and STUFF method, the first character(s) are always your delimiters in the string. Then you use STUFF to remove those characters from the first item. So, if you have a statement with a SELECT with ',' + U.FirstName, before STUFF is applied you have the value ',Bob,Steve,Jane,Sally'. The character you want to get rid of is the first delimiter; which is where STUFF comes in. STUFF({FOR XML PATH},1,1,'') takes the output from FOR XML PATH, and then replaces 1 character, from position 1, with the value ''. In ths example, it replaces ',', making the final string 'Bob,Steve,Jane,Sally'.

Upvotes: 6

Related Questions