Peter Larsson Blog

Patron Saint of Lost Yaks

String concatenation and entitization

This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog.

Consider this sample data


DECLARE @Sample TABLE

        (
            ID INT,
            Data VARCHAR(100)
        )
 
INSERT  @Sample
VALUES  (1, 'Peso & Performance SQL'),
        (1, 'MVP'),
        (2, 'Need help <? /> – '),
        (2, 'With XML string concatenation ?')


The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.


SELECT      i.ID,
            STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM        (
                SELECT      ID
                FROM        @Sample
                GROUP BY    ID
            ) AS i
CROSS APPLY (
                SELECT  ', ' + w.Data
                FROM    @Sample AS w
                WHERE   w.ID = i.ID
                FOR XML PATH(''),
                        TYPE
            ) AS f(Content)
       
 And to deal with characters having ascii values less than 32 (space), you can use this


;WITH cteSource(ID, Content)
AS (
        SELECT      i.ID,
                    f.Content.value('.', 'NVARCHAR(MAX)')
        FROM        (
                        SELECT      ID
                        FROM        @Sample
                        GROUP BY    ID
                    ) AS i
    CROSS APPLY     (
                        SELECT  CAST(', ' + w.Data AS VARBINARY(MAX))
                        FROM    @Sample AS w
                        WHERE   w.ID = i.ID
                        FOR XML PATH(''),
                                TYPE
                    ) AS f(Content)
)
SELECT  ID,
        STUFF(CAST(Content AS NVARCHAR(MAX)), 1, 2, '') AS Content
FROM    (
            SELECT  ID,
                    CAST(N'' AS XML).value('xs:base64Binary(sql:column("Content"))', 'VARBINARY(MAX)') AS Content   
            FROM    cteSource
        ) AS d