Saturday, April 04, 2015

Convert Oracle Number to Hex and Vice Verse

To convert a hexadecimal number (base 16) to a decimal number, we use to_number function and specify the format as 'XXXXX' as shown below.

SQL> select to_number('30003','XXXXXX') from dual;

TO_NUMBER('30003','XXXXXX')
---------------------------
       196611
We can verify the conversion by using the following query.The fifth position from the right represents 16 to the 4th power.
SQL> select power(16,4)*3+3 from dual;

POWER(16,4)*3+3
---------------
         196611
To convert a decimal number to a hex number, we use to_char() function.
SQL> select to_char(196611, 'XXXXXX') from dual;

TO_CHAR
-------
  30003

No comments: