OraQA

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

SQL> exec :base := 8
SQL> exec :input := '1111011'

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

SELECT regexp_replace(XMLAgg(XMLElement(X, str) ORDER BY grp desc),'<X>|</X><X>|</X>') RESULT
FROM
(SELECT substr('0123456789ABCDEF', SUM(num)+1,1) str, grp
 FROM
 (SELECT num * POWER(2,row_number() over (partition by grp order by rn)-1) num, grp
  FROM
  (SELECT doc.extract('/X/text()').getStringVal() as num, rownum rn,
          last_value(case when mod(rownum, a.base) = 1 then rownum end ignore nulls)
          over (order by rownum ) as grp
   FROM
   (SELECT ceil(sqrt(:base)) base, :input as input, length(:input) len FROM DUAL) a,
    TABLE(xmlSequence(extract(XMLType('<DOC>'||
         REGEXP_REPLACE(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw
     (lpad(a.input, a.len +
      CASE WHEN a.base = a.base - MOD(a.len, a.base) THEN 0
                  ELSE a.base - MOD(a.len, a.base ) END
   ,'0')))),'(.)','<X>\1</X>')
  ||'</DOC>'),'/DOC/X'))) doc
  )
 )
 GROUP BY grp
);
RESULT
--------------------------------------------
173                                                                            

SQL> exec :base := 16

SQL> /

RESULT
--------------------------------------------
7B

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