OraQA

The following SQL pattern can be used to convert Octal/Hex to Binary number in SQL.

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

————————————SQL Solution————————————

SELECT utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(to_char(str)))) AS RESULT
FROM
(WITH DATA AS
 (SELECT SUM( num * POWER(base, rn -1) ) base_10_num
  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,
    utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(to_char(:input))))
          as input FROM DUAL) a,
   TABLE(xmlSequence(extract(XMLType('<DOC>'||
   REGEXP_REPLACE(a.input,'(.)','<X>\1</X>')||'</DOC>'),'/DOC/X'))) doc
  )
 )
SELECT max(replace(sys_connect_by_path(sign(bitand(base_10_num, power(2,LEVEL-1))),','),','))
         AS str
FROM (SELECT base_10_num FROM DATA) a
CONNECT BY power(2,LEVEL - 1)<= base_10_num
);
RESULT
------------------
1111011                                                                        

SQL> exec :base := 8

SQL> exec :input := '173'

SQL> /

RESULT
------------------
1111011                                                                        

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