How to Convert Decimal to Hexadecimal using TO_CHAR() in Oracle

In Oracle Database, you can use the TO_CHAR() function to convert a number to its hexadecimal equivalent. To do this, use the X format element.

Example

Here’s an example to demonstrate:

SELECT TO_CHAR(15, 'X')
FROM DUAL;

Result:

   TO_CHAR(15,'X') 
__________________ 
 F                

F is the hexadecimal equivalent to 15 and so that’s what’s returned.

Leading Zeros

You can precede this element only with 0 (which returns leading zeroes) or FM (which suppresses padding).

Here’s what happens when we precede it with 0

SELECT TO_CHAR(15, '0X')
FROM DUAL;

Result:

   TO_CHAR(15,'0X') 
___________________ 
 0F                

Suppress Leading Blanks

If you specify neither 0 nor FM with X, then the return value always has one leading blank.

Here’s what happens when we use the FM format modifier to suppress padding:

SELECT TO_CHAR(15, 'fm0X')
FROM DUAL;

Result:

   TO_CHAR(15,'FM0X') 
_____________________ 
0F                    

The leading space has disappeared.

Uppercase vs Lowercase

Passing an uppercase X results in an uppercase hex value and passing a lowercase x results in a lowercase hex value:

SELECT  
    TO_CHAR(15345, 'fm0XXXX'),
    TO_CHAR(15345, 'fm0xxxx')
FROM DUAL;

Result:

   TO_CHAR(15345,'FM0XXXX')    TO_CHAR(15345,'FM0XXXX') 
___________________________ ___________________________ 
03BF1                       03bf1                      

Non Integers

If the number is not an integer, then it is rounded to the nearest integer:

SELECT  
    TO_CHAR(12.49, 'fm0xxxx') AS "12.49",
    TO_CHAR(12.50, 'fm0xxxx') AS "12.50"
FROM DUAL;

Result:

   12.49    12.50 
________ ________ 
0000c    0000d