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.
