In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 4. Since you can only see this if you are up to this level in the challenge, let me explain it to you. In part one of this puzzle, we are to take a given seed, and prefix that to a number to find the lowest number that will generate a MD5 hash that starts with five zeros.

There are three steps to this process:

  1. Generate a series of numbers. (does this sounds like a really good use for the virtual tally table to you?)
  2. Generate a MD5 hash with the seed and this number.
  3. Get the lowest number that generates a MD5 hash that starts with five zeros.

This time, I’ll show the code, and then explain it.

As it was suggested earlier, I will indeed use a virtual tally table to generate the series of numbers. Next, the SELECT statement does a TOP 1 in conjunction with ORDER BY and WHERE clauses to get the first matching value. The HASHBYTES function specifies to generate a MD5 hash from the seed and the current number (this is performed in the CROSS APPLY since I need to use it in two places – once to display the hash, and the second in the where clause. Performing this in the CROSS APPLY allows me to introduce a computed column for use anywhere in the query after it has been introduced). Finally, in the WHERE clause, this is converted to a character field using the CONVERT function with a style of 2 (since a varbinary starts with 0x, we need to get rid of that). Additionally, if we try to just convert it without a style option, it will try to convert the varbinary to the textual equivalent – not the hash. So, style 2 is used to convert it into the hash without the leading 0x. And there we have our simple query to get the lowest number matching the seed value.

For part 2, we just need to change this to get the first number that generates a seed with six leading zeros. For the first attempt, the obvious change is to just change the LEFT function to get the first six characters, and to compare it to six zeros. However, when you run this, you don’t get a result… the smallest number is higher than the capacity of this virtual tally table. So, we change the virtual tally table to generate a billion rows instead (CROSS JOIN to the Hundreds CTE four times), and now we get the number that we need.

And there we go… two scripts (with very minor changes between them) to get the lowest number that generates a MD5 hash with either five or six leading zeros. They used a virtual tally table, the HASHBYTES function, and the CONVERT function with style 2.