Hexadecimal to Binary
586006Jun 7 2012 — edited Jun 8 2012Hi,
I am using this query to convert hexadecimal data to binary data which I got. But this query is trimming the leading 0 if it is there whereas I want to show all the data including leading 0 if it should be there. Please suggest.
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
);