OraQA

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

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.