Just A Programmer We're just programmers

9Jan/126

Using PowerShell to represent Base 26 as the uppercase English Alphabet

Today I was asked to do something that seemed simple, until I actually had to do it. A coworker had a database with two fields he wanted renamed in a specific way. For our example, lets call them ProductNumber and ProductName. He wanted ProductNumber to be sequential (1, 2, 3 . . .) and the ProductName fields to be called “Product A”, “Product B” . . . “Product Z”, “Product AA” etc. So this suddenly became a non-trivial problem if you had more than 26 rows, which of course I did.

So I rolled up my sleeve, got a fresh cup of coffee, and got to work. Populating ProductNumber was easy enough using a Common Table Expression (CTE) with a ROW_NUMBER(). Then I realized I could think of the English alphabet as symbols for a base 26 number system, with AA following Z and so on. The only problem was I couldn’t express that in a set based way for a clean T-SQL implementation. No problem, I’d just generate the T-SQL to make a giant mapping table in PowerShell!

I am ashamed to admit I had to look up the algorithim for converting from base 10 to another number. I was also surprised to discover that the first result google returned me was this tripod page.

The algorithm is as follows.

  1. Start with an empty string which becomes the return value
  2. While the value is greater than the base get the remainder of the value divided by the base. Convert that to its letter and prepend that to the return value
  3. Repeat step 2 with the quotient of the value over the base.
  4. When the quotient is less than the base, prepend that to the string instead.

It seemed simple enough, but there were some headaches.

The first thing I discovered was that when you divide integers in PowerShell, you get a float as a result. Also casting it back to an int rounds instead of truncating the results. I was expecting the opposite in both cases, because that is how C# behaves. I ended up using the unwieldy combination of Math.Floor() and a cast in the form [int][math]::Floor($currVal / 26) to resolve this. The MSDN technet has an article that recommends the more unwieldy [Math]::floor([int] $currVal / [int] 26), but I proved that my terser method gives the same results.

Then I had problems with how to display powers of 26. The way it was supposed to work was that 1 = A, 24 = X, 25 = Y, 26 = Z and 27 = AA. However, depending on how I did it I ended up with 26 = AZ or 27 = BA. I could not account for this edge case, nor compensate for it with special conditions.

Then it dawned on me, A needed to be equal to zero not one. A base 10 system deals with the digits 0-9. Base 2 deals with 0 and 1. Base 16 deals with 0-F and F is 15. Once I rewrote my script to work that way, edge cases disappeared, and things just worked.

The script

	function Convert-ToLetters ([parameter(Mandatory=$true,ValueFromPipeline=$true)][int] $value)  {
		$currVal = $value;
		$returnVal = '';
		while ($currVal -ge 26) {
			$returnVal = [char](($currVal) % 26 + 65) + $returnVal;
			$currVal =  [int][math]::Floor($currVal / 26)
		}
		$returnVal = [char](($currVal) + 64) + $returnVal;
		
		return $returnVal
	}

If its not clear how I generated upper case letters, the ASCII codes for A through Z are 65 through 90, and casting an integer to a char converts it to its ASCII code. Ergo, the expression [char]65 evaluates to “A”.

So now here’s the function in action:

1 .. 100 | ForEach-Object {
	$_ | Convert-ToLetters
}

Happy Scripting!

  • Pingback: Justin Dearing

  • Pingback: Justin Dearing

  • Pingback: Mr Power Scripts

  • Guest

    Hey just tried your script, I get AA instead of Z, if I change 26 to 27 in the while loop: ($curval -ge 27) I get the letter Z as expected but then 27 becomes AB…

    • http://www.justaprogrammer.net Justin Dearing

      Hrm you are correct, there seems to be a bug around addign new digits.  I need to tweak it some more. Here is slightly different buggy behavior:

      function Convert-ToLetters ([parameter(Mandatory=$true,ValueFromPipeline=$true)][int] $value)  {
          $currVal = $value;
          $returnVal = '';
          while ($currVal -gt 26) {
              $returnVal = [char](($currVal) % 26 + 65) + $returnVal;
              $currVal =  [int][math]::Floor($currVal / 26) 
          }
          $returnVal = [char](($currVal) + 64) + $returnVal;
           
          return $returnVal
      }

      1..3+ 24..28 + 50..54 + 17574..17578 + 18250..18254 | %{ New-Object PSObject -Property @{ 'Number' = $_; 'Letters' = (Convert-ToLetters $_) } } |Format-Table -AutoSize

    • Guest

      I’ve knocked up a simple script for A to ZZ which is all I need :) Hope you can find the bug, looks like a good script!

      function Simple-Convert-ToLetters ([parameter(Mandatory=$true,ValueFromPipeline=$true)][int] $value)  {
      # Simple conversion of letters to numbers from 1 (A) to 702 (ZZ)
      $numHash = @{ Num1 = “A”; Num2 = “B”; Num3 = “C”; Num4 = “D”; Num5 = “E”; Num6 = “F”;Num7 = “G”;Num8 = “H”;Num9 = “I”; Num10 = “J”;Num11 = “K”;Num12 = “L”;Num13 = “M”;Num14 = “N”;Num15 = “O”;Num16 = “P”;Num17 = “Q”;Num18 = “R”;Num19 = “S”;Num20 = “T”;Num21 = “U”;Num22 = “V”;Num23 = “W”;Num24 = “X”;Num25 = “Y”;Num26 = “Z”}
      if ($value -le 702){  $currVal = $value; if ($currVal -gt 26){  $highVal = $currVal  if ($highVal/26 -is [int]){$highVal = $highVal – 1}  $i = [Math]::floor($highVal / 26)  $letter = $numHash["Num$i"]    do{     $currVal = $currVal – 26      }   while ($currVal -gt 26)   $letter = $letter + $numHash["Num$currVal"] }  else {  $letter = $numHash["Num$currVal"] } return $letter}
       else { “Error – Number is greater than 702 (ZZ)” }}
      1..703 | ForEach-Object {Write-Host “”; Write-Host “$_ equals”; $_ | Simple-Convert-ToLetters}

  • Friendly guy

    I know it has been over 2 years since this post… Just trying to help :)

    Change the “-ge 26″ in the while expression to “-ge 27″. You also have to change the “+ 65″ in the while loop to “+ 64″. That should give you the results you wanted :)

    (P.S. I found this post because it was mentioned on this other post http://blog.commandlinekungfu.com/2014/04/episode-177-there-and-back-again.html?m=1 )

    • http://www.justaprogrammer.net Justin Dearing

      Thanks for sharing. I’ll try to test your fix so I can update the blog post.

  • iyshannon

    I was at your SQLCLR talk yesterday and was checking out your blog when I came across this post. The problem is line 8 should be “+ 65″ rather than “+ 64″. The lines inside the loop are using “+ 65″ (so A is zero), but line 8 is using “+ 64″ (so A is one). So, the first character of the output is off by 1. After making this change, it outputs 1 = B, 25 = Z, 26 = BA, 27 = BB, etc.