How to create incrementing base 62 alphanumeric sequence in SQL Server

Question

I would like to create an incrementing alphanumeric sequence for a SQL server table, that uses numbers 0-9, lowercase letters a-z, and uppercase letters A-Z. So the series would be like 0, 1, 2, 3...9, a, b, c, d..., x, y, z, A, B, C, D...X, Y, Z and then beyond that it would become 11, 12, 13... 1a, 1b, 1c... 1X, 1Y, 1Z, then 21, 22, 23... 2A, 2B, and so on.

The goal is to have an incrementing string ID that is short in characters, essentially incrementing using a base 62 alphanumeric system.

My first inclination is to create a table with many columns, and each column would store a place in the system. So columns like 0,0,0,0,0,1; and each time a new value was needed it would loop through the columns, incrementing as needed, eventually reaching something like 0,0,0,3,D,z, and so on.

Does anyone have a more clever or concise idea on how to do this? Again, the goal is to create an incrementing sequence that is "short" in terms of the number of places or characters it takes to represent.

Answer 1

Credit to @JohnCappelletti and Daniel Ballinger for the answer found here: http://www.fishofprey.com/2011/08/convert-between-base-10-and-base-62-in.html

To document:

Convert from Base 10 to Base 62 in T-SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Base62Encode](@a_number_to_convert [decimal](36, 0))
RETURNS [char](12) WITH EXECUTE AS CALLER
AS 
BEGIN

DECLARE @v_modulo INTEGER;  
DECLARE @v_temp_int decimal(38) = @a_number_to_convert;  
DECLARE @v_temp_val VARCHAR(256) = '';  
DECLARE @v_temp_char VARCHAR(1);    

--DECLARE @c_base62_digits VARCHAR(62) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
DECLARE @c_base62_digits VARCHAR(62) = '0123456789aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ'; 

 IF ( @a_number_to_convert = 0 )
 BEGIN
     SET @v_temp_val = '0';  
 END

 WHILE ( @v_temp_int <> 0 )
 BEGIN
     SET @v_modulo = @v_temp_int % 62;  
     SET @v_temp_char = substring( @c_base62_digits, @v_modulo + 1, 1 );  
     SET @v_temp_val = @v_temp_char + @v_temp_val;   
     SET @v_temp_int = floor(@v_temp_int / 62);  

 END

 RETURN @v_temp_val;  

END

Convert from Base 62 to Base 10 in T-SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Base62Decode](@a_value_to_convert [char](12))
RETURNS [decimal](36, 0) WITH EXECUTE AS CALLER
AS 
BEGIN

DECLARE @v_iterator int;  
DECLARE @v_length int;  
DECLARE @v_temp_char VARCHAR(1);  
DECLARE @v_temp_int bigint;  
DECLARE @v_return_value decimal(38) = 0;  
DECLARE @v_multiplier decimal(38) = 1;  
DECLARE @v_temp_convert_val VARCHAR(256) = @a_value_to_convert;  

--DECLARE @c_base62_digits VARCHAR(62) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
DECLARE @c_base62_digits VARCHAR(62) = '0123456789aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ'; 


SET @v_length = len( @v_temp_convert_val );  
SET @v_iterator = @v_length; 

WHILE ( @v_iterator > 0 )   
BEGIN
    -- The character being converted
    SET @v_temp_char = substring( @v_temp_convert_val, @v_iterator, 1 );  
    -- The index of the character being converted
    SET @v_temp_int = charindex( @v_temp_char collate  SQL_Latin1_General_CP1_CS_AS, @c_base62_digits collate  SQL_Latin1_General_CP1_CS_AS ) - 1;  

    SET @v_return_value = @v_return_value + ( @v_temp_int * @v_multiplier );  
    SET @v_multiplier = @v_multiplier * 62;  
    SET @v_iterator = @v_iterator - 1;  

END

RETURN @v_return_value; 

END