Posted on January 8, 2016
Base conversion in SQL
Here’s something silly I threw together that no one will probably use, which makes it perfect for posting here. We ran across software that has a table that has an Id that ALSO has a unique char(4) field that, as far as we can tell, serves no rational purpose. But the values need to be unique and alphanumeric, apparently with no special characters at all. The Id field is a positive integer that starts at 1, so it seemed like a fine opportunity to convert to base 62, using 0-9,A-Z, and a-z as the 62 candidate numerals.
We ended up not doing this, since there was more complicated slower and more difficult to comprehend code available, but I still think this is useful:
ALTER FUNCTION [dbo].[Base62Convert] ( @InputInt BIGINT ) RETURNS CHAR(4) AS -- Input: Positive Integer BEGIN DECLARE @Result CHAR(4) = '' IF (@InputInt < 0) return cast('No negative numbers allowed.' as int); IF (@InputInt >= POWER(62,4)) return cast('Number too big, going home.' as int); WHILE (@InputInt > 0) BEGIN SET @Result = CHAR(@InputInt % 62 + CASE WHEN @InputInt % 62 < 10 THEN 48 WHEN @InputInt %62 < 36 then 55 ELSE 61 END) + @Result SET @InputInt = FLOOR(@InputInt/62) END RETURN RIGHT('0000' + @Result, 4) END GO -- Test: select dbo.base62Convert(power(62,4)-1), power(62,4)-1
Which will give you nice unique 4-character identifiers up to 14,776,335.