Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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
 
 

Print | posted on Sunday, July 04, 2010 11:59 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET