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…
There are good reasons to not do something like that manually.
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.
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.
;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.