SQL for Converting Decimal Integers to Any Base

The following SQL statement converts an unsigned decimal integer d to a base b.

select &&d base10,
    listagg(
        substr(
          case &&b when 32 then 
            '0123456789abcdfghjkmnpqrstuvwxyz'
          else
            '0123456789abcdefghjkmnpqrstuvwxyz'
          end,
          mod(
            trunc(&&d/power(&&b,level-1)),
            &&b
          ) + 1 ,
          1
        ) 
    ) within group (order by level desc) base&&b 
  from dual 
  connect by level <= ceil(log(&&b,&&d+1))
;

You can specify any base to convert to, for example:

define d = 258
column base10 format 999999

define b = 2
column base&&b format a9

select &&d base10,
    listagg(
        substr(
          case &&b when 32 then 
            '0123456789abcdfghjkmnpqrstuvwxyz'
          else
            '0123456789abcdefghjkmnpqrstuvwxyz'
          end,
          mod(
            trunc(&&d/power(&&b,level-1)),
            &&b
          ) + 1 ,
          1
        ) 
    ) within group (order by level desc) base&&b 
  from dual 
  connect by level <= ceil(log(&&b,&&d+1))
;

 BASE10 BASE2
------- ---------
    258 100000010
	
define b = 8
column base&&b format a9
/

 BASE10 BASE8
------- ---------
    258 402

define b = 16
column base&&b format a9
/

 BASE10 BASE16
------- ---------
    258 102

define b = 32
column base&&b format a9
/

 BASE10 BASE32
------- ---------
    258 82

The conversion is performed by doing Euclidean division of the integer in the decimal notation with the new base. This process is iteratively applied to the quotient until it becomes zero. Finally, all of the remainders are concatenated in the reversed order with the single-set aggregate function listagg.

As SQL doesn't explicitly support iterations, I used a hierarchical query for generating quotients and remainders instead. The total number of divisions n can be calculated as follows:

n = ceil(log(b,d+1))

A separate encoding table '0123456789abcdfghjkmnpqrstuvwxyz' is defined for the base32, because the table doesn't contain "e" in the Oracle database.

References:

• Positional Notation, Wikipedia
• SQL_ID is just a fancy representation of hash value, Tanel Poder