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'
-----------------------------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