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.<\/p>\n
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:<\/p>\n
ALTER FUNCTION [dbo].[Base62Convert] ( @InputInt BIGINT ) RETURNS CHAR(4)\r\nAS\r\n-- Input: Positive Integer\r\nBEGIN\r\n DECLARE @Result CHAR(4) = ''\r\n\r\n IF (@InputInt < 0)\r\n return cast('No negative numbers allowed.' as int);\r\n\r\n\tIF (@InputInt >= POWER(62,4))\r\n\t\treturn cast('Number too big, going home.' as int);\r\n\r\n WHILE (@InputInt > 0)\r\n BEGIN\r\n SET @Result = CHAR(@InputInt % 62 + \r\n\t\tCASE WHEN @InputInt % 62 < 10 THEN 48\r\n\t\t\tWHEN @InputInt %62 < 36 then 55 \r\n\t\t\tELSE 61 END)\r\n\t\t+ @Result\r\n\tSET @InputInt = FLOOR(@InputInt\/62)\r\n END\r\n\r\n RETURN RIGHT('0000' + @Result, 4)\r\nEND\r\n\r\nGO\r\n\r\n-- Test:\r\nselect dbo.base62Convert(power(62,4)-1), power(62,4)-1\r\n<\/pre>\nWhich will give you nice unique 4-character identifiers up to 14,776,335.<\/p>\n
Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"