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.

Enjoy!