Convert Between Base 10 and Base 62 in PL/SQL

There are many reasons why you may find yourself needing to convert a value back and forth between base 10, base 62, base 36, hexadecimal or whatever. I found myself in a similar situation where I had to generate a unique id entries in a data file, yet I only had two characters where I could put this value. Since this file could possibly have up to 3,000 entries in it, I was not going to be able to just use numbers since that only gives me 10 x 10 = 100 possible values ( 0 to 99 ).

However, if I were to translate a sequential decimal ( base 10 ) number to base 62, I now have 62 x 62 = 3,844 possible values ( 0 - zz ). Now obviously for base 62, the value will be case sensitive. If you can't guarantee case sensitivity, then you'll have to use base 36.

Convert from Base 10 to Base 62 in PL/SQL

CREATE OR REPLACE FUNCTION f_to_base62( a_number_to_convert INTEGER ) RETURN VARCHAR2 IS
  
 v_modulo INTEGER;
 v_temp_int INTEGER := a_number_to_convert;
 v_temp_val VARCHAR2(256);
 v_temp_char VARCHAR2(1);
 c_base62_digits CONSTANT VARCHAR2(62) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';

BEGIN

 IF ( a_number_to_convert = 0 ) THEN
   v_temp_val := '0';
 END IF;
      
 WHILE ( v_temp_int <> 0 ) LOOP
   v_modulo := v_temp_int MOD 62;
   v_temp_char := SUBSTR( c_base62_digits, v_modulo + 1, 1 );
   v_temp_val := v_temp_char || v_temp_val;
   v_temp_int := floor(v_temp_int / 62);
 END LOOP;

 RETURN v_temp_val;

END;

Convert from Base 62 to Base 10 in PL/SQL

CREATE OR REPLACE FUNCTION f_from_base62( a_value_to_convert VARCHAR2 ) RETURN INTEGER IS

 v_iterator INTEGER;
 v_length INTEGER;
 v_temp_char VARCHAR2(1);
 v_temp_int INTEGER;
 v_return_value INTEGER := 0;
 v_multiplier INTEGER := 1;
 v_temp_convert_val VARCHAR2(256) := a_value_to_convert;
 c_base62_digits CONSTANT VARCHAR2(62) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';

BEGIN

 v_length := length( v_temp_convert_val );
 v_iterator := v_length;
 WHILE ( v_iterator > 0 ) LOOP
   v_temp_char := SUBSTR( v_temp_convert_val, v_iterator, 1 );
   v_temp_int := INSTR( c_base62_digits, v_temp_char ) -  1;
   v_return_value := v_return_value + ( v_temp_int * v_multiplier );
   v_multiplier := v_multiplier * 62;
   v_iterator := v_iterator - 1;
 END LOOP;

 RETURN v_return_value;

END;