http://www.sqlservercentral.com/blogs/sqlrnnr/2011/03/01/delimiting-results/

Delimiting Results

By Jason Brimhall, 2011/03/01

Recently, I came across the need to delimit some data in order to be able to test some procedures that I was writing.  The procedures need to be able to take a delimited string and convert that into a result set.  You say, why not just do that manually – it would be pretty simple.  Well, it would be easy to do that – but…

Why not do it manually?

There are good reasons to not do something like that manually.

1. It’s not automated and DBAs should automate where possible
2. I could potentially use an automated solution for more tests than just to test this one particular proc (case in point, I am already testing on multiple procs).
3. Human element could create some erroneous data.
4. It’s pretty easy to automate too
5. It’s less tedious

So, how to do it

Just like most things in SQL, there are multiple ways of tackling this particular issue.  I have chosen two methods to demonstrate.  The First method uses COALESCE, while the second uses the STUFF function with XML.

Code block

DECLARE @YourList VARCHAR(1024)
 
;with delimit AS (
SELECT TOP 10
		VP.YourValue AS YourValue
	FROM YourTable VP
 
)
 
SELECT @YourList = COALESCE(@YourList + '|', '') +
   CAST(YourValue AS VARCHAR(20))
FROM delimit
 
SELECT @YourList

In this example, I have used a CTE to pre-populate a table full of values that I want to retrieve from SomeTable.  Then I drop those results into the variable using Coalesce.  This method is extremely fast.  The cast of the Value should be adjusted to an appropriate size.  When I run this particular query I see execution times of about 1ms.

The second option for performing this string concatenation is the STUFF, as previously mentioned.  Here is an example of such a query.

Code block

;with delimit AS (
SELECT TOP 10
		VP.YourValue AS YourValue
	FROM YourTable VP
 
)
SELECT YourList = STUFF((
                   SELECT '|' + CONVERT(VARCHAR(20),YourValue)
                     FROM delimit
                      FOR XML PATH(''),
                              TYPE).VALUE('.','varchar(max)'),1,1,'')

In this example, I have once again used the CTE to pre-populate a list of values that I want to concatenate.  This also helps to keep the comparisons on equal ground, by using the same population methods and ensuring that the list is the same size without consuming too many resources.  With the code as-is, this particular example runs pretty fast and returns my string in about 90ms.

When comparing side by side with this “limited” testing, I would tend to lean towards the COALESCE method.  However, both are good enough for my testing and I could use either depending on the day, or material.