One of the reasons I like Stackoverflow as a place for asking and answering programming questions is that I
- Almost always get what I am after
- Almost always learn something in the process (in answering and asking)
Case in point with a question I asked regarding hierarchy sorting in SQL Server. The question in full can be found on the adjacent link, but, in a nutshell I was looking at how I might sort a variable length hierarchy field in SQL Server 2000.
So, the field value could be 1. Or it could be 126.96.36.199. Or it could be 1.92.3188.8.131.52.7.81231.
Given that I needed to sort by the last digit, then the second-last, etc, etc, AND that the length was variable, I found myself a little stumped as to an elegant solution. So I decided to call upon the generosity of the Stackoverflow community
First-up was a number of fixed length solutions, which while good, didn’t quite help. Then I got some help in SQL 2008, which was unfortunate, as I was wanting it in SQL 2000. And then user Vash popped up with an edit to his original post:
Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here.
Marvellous! Here is the solution (modified sightly) that I adopted:
CREATE FUNCTION dbo.ufn_SortByHierarchy(@s as varchar(500), @delimiter as char(1)) returns VARBINARY(2500) AS BEGIN DECLARE @pos as int, @r as varbinary(2500), @element as int; SET @r = 0x; SET @s = @s + @delimiter; SET @pos = charindex(@delimiter, @s); while @pos > 0 BEGIN SET @element = cast(left(@s, @pos - 1) AS INT); SET @r = @r + CASE WHEN @element = -2147483648 THEN 0x0000000000 WHEN sign(@element) = -1 THEN 0x01 + cast(2147483647 + @element AS BINARY(4)) ELSE 0x02 + cast(@element AS BINARY(4)) END; SET @s = stuff(@s, 1, @pos, ''); SET @pos = charindex(@delimiter, @s); END RETURN @r; END
And, as it turned out, I’d never heard of the varbinary data type, so right there was some extra reading and knowledge for me.
And that, I thought was it. But no! A short while later, user Martin Smith, popped up with a comment.
@James – Not sure what you ended up using. You can also use
parsenamefor this as in this answer: stackoverflow.com/questions/3057532/
A comment! I’d have voted it up if it were an answer. So I followed the link, and voted that up.
So, I also learned about Parsename, which works in lowly SQL 2000 as well. unfortunately, it only works on a four-digit hierarchy, but still useful for me to know about.
So, lots of useful information, new things learned and a permanently documented solution to my problem. That is why I like Stackoverflow.