OraQA

The following SQL pattern can be used to convert Binary, Octal , Hex to Decimal (base ten) number in SQL.

SQL> variable input varchar2(28)
SQL> variable base number
SQL> exec :base := 2
SQL> exec :input := '1111011'

————————–SQL Solution ———————————-

SELECT SUM( num * POWER(base, rn -1) ) result
FROM
(SELECT instr(num_str, upper(doc.extract('/X/text()').getStringVal()))-1 as num, rownum rn, a.base
 FROM
 (SELECT '0123456789ABCDEF' as num_str, :base as base, :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
);
    RESULT
----------
       123
SQL> exec :base := 8
SQL> exec :input := '173'
SQL> /    

    RESULT
----------
       123                                                                         

SQL> exec :base := 16
SQL> exec :input := '7B'    

SQL> /    

    RESULT
----------
       123

----------------Simple Solution for convert Hex to Decimal only-------------------- 
SQL> exec :input := '7B'   

SELECT  to_number( :input , 'xxxx' ) result FROM dual;

    RESULT
----------
       123

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