I stumbled across the task of sorting numbers present in a comma separated string, based on their numerical value in SQL.
Curious as always I thought it would be possible to solve such a problem using XML and XQuery in SQL and indeed it was.
Given a numerical string, then in SQL Server 2005 and up, you can do something like this:
DECLARE @X XML = ''
DECLARE @STR VARCHAR(255) = CAST(@X.query('for $i in (11, 9, 10)
order by $i
return fn:concat(xs:string($i), ",")') AS VARCHAR(255))
SELECT LEFT(@STR, LEN(@STR) -1)
(Remember, in SQL 2005, you need to put assignment on another line, this is 2008 syntax)
The output from this query will be the value: '9, 10, 11'
Now, it might not be special or terrible useful as such – but the point of the post is also much more that untraditional methods for solving problems exists, and one can think outside the box and solve many problems by doing so.
A problem with this is that if you want the input created dynamically – you either need to build the entire segment within a string and use EXEC, or perhaps you can use the sql:column and sql:variable to help you along.
