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.
- Start with an empty string which becomes the return value
- 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
- Repeat step 2 with the quotient of the value over the base.
- 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!