sqloracle-databasefunctionhexto-char

Convert fractional number to Hexadecimal in Oracle


I have a table in database with fractional value- value 14.65 45 7458.34 34.69 4.7 34

I want to convert this fractional value to hexadecimal.

But to_hex function only converts decimal value(14) and not (14.65).

Tried with to_char also- SELECT TO_CHAR(15.33, 'X') FROM DUAL; --->output: F

I don't want to ceil or floor the value and print Hexadecimal number. I want 15.33 --->F.547AE147


Solution

  • Split the values into the integer and the decimal parts and use TO_CHAR on them separately:

    WITH table_name (value) AS (
      SELECT  15.33 FROM DUAL UNION ALL
      SELECT -15.33 FROM DUAL
    )
    SELECT value,
           CASE WHEN value < 0 THEN '-' END
           || TO_CHAR(TRUNC(ABS(value)), 'fmXXXXXXXXXX')
           || '.'
           || TO_CHAR(
                ABS(MOD(value, 1))*TO_NUMBER('FFFFFFFF', 'XXXXXXXX'),
                'fm0XXXXXXX'
              ) AS hex
    FROM   table_name;
    

    Which outputs:

    VALUE HEX
    15.33 F.547AE147
    -15.33 -F.547AE147

    db<>fiddle here