Convert between base 10 and base 62 in T-SQL

These functions can be used to convert from base 10 to base 62 encoding and vice versa. They were converted from Convert Between Base 10 and Base 62 in PL/SQL by Michael.

Note: I changed the character order in @c_base62_digits to alter the order the characters were applied in.

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

See also:

• Distance Between Salesforce Ids. Includes Apex code to decode Base62 to a long.