OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to convert Decimal number to Binary/Octal/Hex in SQL

February 29th, 2008 By Frank Zhou

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

SQL> COLUMN Result FORMAT A18
SQL> COLUMN to_binary FORMAT A28
SQL> variable input varchar2(28)
SQL> variable base number
SQL> exec :base := 8
SQL> exec :input := '123'

----------------------------------------------10G SQL Solution---------------------------------------------------

SELECT regexp_replace(XMLAgg(XMLElement(X,n) ORDER BY rn desc), '<X>|</X><X>|</X>') Result,
max(to_bin) to_binary
FROM
(SELECT max(to_bin) to_bin, rn,
substr('0123456789ABCDEF', sum(TO_NUMBER(SUBSTR(num,LEVEL,1)) * POWER(2,LEVEL - 1))+1,1) n
FROM
( WITH BINARY AS
(SELECT max(replace(sys_connect_by_path(sign(bitand(num, power(2,LEVEL-1))),','),',')) AS str
FROM (SELECT :input AS num FROM DUAL) connect by power(2,LEVEL - 1)<= num
)
SELECT doc.extract('/X/text()').getStringVal() as num, rownum rn, to_bin
FROM
(SELECT ceil(sqrt(:base)) base, length(str) len, str,
utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(to_char(str)))) to_bin
FROM BINARY) a,
TABLE(xmlSequence(extract(XMLType('<DOC>'||REGEXP_REPLACE(rpad(a.str, 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'),
CASE a.base
WHEN 3 THEN '(.{3})'
WHEN 4 THEN '(.{4})' END ,'<X>\1</X>')||'</DOC>'),'/DOC/X'))) doc
)
CONNECT BY LEVEL <= length(num)
AND PRIOR rn = rn
AND PRIOR dbms_random.string('p',20) IS NOT NULL
GROUP BY rn
);

RESULT TO_BINARY
------------------ ----------------------------
173 1111011

SQL> exec :base := 16

SQL> /

RESULT TO_BINARY
------------------ ----------------------------
7B 1111011

--------------Simple Solution for converting Decimal to Hex only-------------

SQL> exec :input := '123'

SELECT to_char(:input, 'XXXX' ) result FROM dual;

RESULT
-------
7B

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question