Saturday, March 29, 2014

TSQL STUFF with XML PATH: Delimit a Series of Strings

Last post, we explored some useful cases where STUFF function can be used. This post we'll take a step further by combining STUFF with XML PATH to combine rows to a single string and separate the values with commas.

Let's remind ourselves the STUFF function syntax:
STUFF ( original_string, start_position , length , replaceWith_string )

 /* Delimit a string of text. Insert a comma between strings.*/
 DECLARE @LogicalQueryProccessingOrder TABLE (
phase VARCHAR(10)

INSERT INTO @LogicalQueryProccessingOrder 

/* this doesn't work because result has an extra comma at the end. Note this return in xml format */
SELECT phase + ', '
FROM @LogicalQueryProccessingOrder

/* this one works */
       /* 1st parameter: original string */
       (SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH(''))
       /* 2nd parameter:  start position. This is at last comma position.
        , LEN((SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH('')))
       /* 3rd parameter: length: number of characters to be replace. This will delete the extra comma at the end. */
      , 1
       /* 4th parameter: string to be replace with. An empty string will act like an eraser. This will replace the last comma with an empty string */
    , '') AS LogicalQueryOrder; 

/* same result here */
SELECT STUFF((SELECT ', ' + phase FROM @LogicalQueryProccessingOrder FOR XML PATH('')) 
      , 1 
     , 1 
      ,'') AS LogicalQueryOrder; 

No comments:

Post a Comment