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

Have you ever found yourself wanting to test out a bit of HTML/CSS/JavaScript without having access to an IDE or being bothered to fire it up?

Do you want to hack prototype a bit of code quickly without fuss?

Or maybe you’re one of those StackOverflow answerers that Race To Get The First Answer In.

If your answer is ‘yes’ or ‘I am actually’ then there’s probably a myriad of different resources you can call upon to help out, but you might consider JSFiddle

I created  a really simple bit of code:

HTML

<input type="checkbox" />

JavaScript/jQuery

$("input").click(function(){alert("test");});

Then I selected ‘jQuery 1.4.2.’ from the ‘Choose Framework’ section and clicked run.

When I clicked on the checkbox, everything hung together like a dream:

At the time of writing the blurb stated that it was still under heavy development, however I still thought it was a genuinely nice little tool that I wanted to share.

HTML, JavaScript, jQuery, Stackoverflow, Tools