Andrew Fraser DBA

Extended Hexadecimal in Oracle

Standard decimal > hexadecimal is done with:

select to_char ( 10 , 'x' ) from dual ;

But I wanted a kind of extended hexadecimal that would keep single letters going up beyond 15/f, so would return ‘g ‘for decimal 16 instead of returning hexadecimal ’10’.

Why did I want that? I needed to squeeze a 2 digit number column into a 1 character column in an extract transform load operation. Most of the source data was less than 16, but there were a few values higher than that, and a simple decimal > hexadecimal translation wouldn’t work for those.

The ascii value for ‘a’ is 97, so using the chr function to turn numbers into their ascii character equivalents gives:

SELECT CASE
           WHEN numcol BETWEEN  0 AND  9 THEN TO_CHAR ( numcol )
           WHEN numcol BETWEEN 10 AND 35 THEN CHR ( 87 + numcol )
       END AS extended_hexadecimal
  FROM mytable ;