How to convert a number from any base to any base in SQL
April 19th, 2008 By Frank Zhou
The following SQL pattern can be used to convert a number in any base to any other base (Base <=16 ) in SQL.
variable input varchar2(28)
variable FromBase number
variable ToBase number
exec :FromBase:= 16
exec :ToBase:= 8
exec :input := '7B'
SELECT base_str_num
FROM
(SELECT SUM(num * POWER(FromBase, rn -1)) Base_10_input
FROM
(SELECT instr(num_str, upper(doc.extract('/X/text()').getStringVal()))-1 as num,
rownum rn, FromBase
FROM
(SELECT '0123456789ABCDEF' as num_str, :FromBase as FromBase,:input as input FROM DUAL) a,
TABLE(xmlSequence(extract(XMLType('<DOC>'||
REGEXP_REPLACE(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(a.input)))
,'(.)','<X>\1</X>')||'</DOC>'),'/DOC/X'))) doc
))
MODEL
DIMENSION BY (0 x)
MEASURES (CAST(NULL AS varchar2(4000)) base_str_num, Base_10_input, :ToBase as ToBase)
RULES ITERATE (3999) UNTIL (Base_10_input[0] = 0)
(base_str_num[0] = substr('0123456789ABCDEF',mod(Base_10_input[cv()],ToBase[cv()])+1,1)||base_str_num[cv()],
Base_10_input[0]= trunc(Base_10_input[cv()] / ToBase[cv()])
);
BASE_STR_NUM
-----------------------
173
exec :FromBase:= 8
exec :ToBase:= 2
exec :input := '173'
SQL> /
BASE_STR_NUM
------------------------
1111011
exec :FromBase:= 16
exec :ToBase:= 5
exec :input := '10'
SQL> /
BASE_STR_NUM
------------------------------
31
