James Wiseman

One of the reasons I like Stackoverflow as a place for asking and answering programming questions is that I

1. Almost always get what I am after
2. 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 1.2.3.4. Or it could be 1.92.333.14.58.62.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 parsename for 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.

Hierarchy, Sorting, SQL Server, Stackoverflow