{"id":2073,"date":"2018-07-03T22:39:36","date_gmt":"2018-07-03T22:39:36","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=2073"},"modified":"2018-07-03T22:39:36","modified_gmt":"2018-07-03T22:39:36","slug":"sql-converting-decimal-integers-to-any-base","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/sql-converting-decimal-integers-to-any-base\/","title":{"rendered":"SQL for Converting Decimal Integers to Any Base"},"content":{"rendered":"

The following SQL statement converts an unsigned decimal integer d<\/I> to a base b<\/i>.<\/p>\n

select &&d base10,\r\n    listagg(\r\n        substr(\r\n          case &&b when 32 then \r\n            '0123456789abcdfghjkmnpqrstuvwxyz'\r\n          else\r\n            '0123456789abcdefghjkmnpqrstuvwxyz'\r\n          end,\r\n          mod(\r\n            trunc(&&d\/power(&&b,level-1)),\r\n            &&b\r\n          ) + 1 ,\r\n          1\r\n        ) \r\n    ) within group (order by level desc) base&&b \r\n  from dual \r\n  connect by level <= ceil(log(&&b,&&d+1))\r\n;<\/code><\/pre>\n
You can specify any base to convert to, for example:<\/p>\n
define d = 258\r\ncolumn base10 format 999999\r\n\r\ndefine b = 2\r\ncolumn base&&b format a9\r\n\r\nselect &&d base10,\r\n    listagg(\r\n        substr(\r\n          case &&b when 32 then \r\n            '0123456789abcdfghjkmnpqrstuvwxyz'\r\n          else\r\n            '0123456789abcdefghjkmnpqrstuvwxyz'\r\n          end,\r\n          mod(\r\n            trunc(&&d\/power(&&b,level-1)),\r\n            &&b\r\n          ) + 1 ,\r\n          1\r\n        ) \r\n    ) within group (order by level desc) base&&b \r\n  from dual \r\n  connect by level <= ceil(log(&&b,&&d+1))\r\n;\r\n\r\n BASE10 BASE2\r\n------- ---------\r\n    258 100000010\r\n\t\r\ndefine b = 8\r\ncolumn base&&b format a9\r\n\/\r\n\r\n BASE10 BASE8\r\n------- ---------\r\n    258 402\r\n\r\ndefine b = 16\r\ncolumn base&&b format a9\r\n\/\r\n\r\n BASE10 BASE16\r\n------- ---------\r\n    258 102\r\n\r\ndefine b = 32\r\ncolumn base&&b format a9\r\n\/\r\n\r\n BASE10 BASE32\r\n------- ---------\r\n    258 82<\/code><\/pre>\n
The conversion is performed by doing Euclidean division<\/a> 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<\/a>.<\/p>\n
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:<\/p>\n
n = ceil(log(b,d+1))<\/p>\n
A separate encoding table '0123456789abcdfghjkmnpqrstuvwxyz' is defined for the base32, because the table doesn't contain \"e\" in the Oracle database.<\/p>\n
References:<\/p>\n