'Signed integer to signed hexadecimal 2's compliment in oracle

I have a sample negative number in a column of a table. e.g -1000

Expected Output- FC18

I want the output as FC18 in oracle SQL, which is Signed Hexadecimal 2's compliment

Reference- https://www.rapidtables.com/convert/number/decimal-to-hex.html?x=-1000

sqloracle


Solution 1:[1]

You can use:

SELECT value,
       TO_CHAR(
         CASE
         WHEN value >= POWER(2,15) OR value < -POWER(2, 15)
         THEN NULL
         WHEN value < 0
         THEN POWER(2,16)
         ELSE 0
         END + value,
         'FM000X'
       ) AS int_to_2c_hex
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT  +1000 AS value FROM DUAL UNION ALL
  SELECT  -1000 FROM DUAL UNION ALL
  SELECT     -1 FROM DUAL UNION ALL
  SELECT     +1 FROM DUAL UNION ALL
  SELECT +32767 FROM DUAL UNION ALL
  SELECT -32768 FROM DUAL UNION ALL
  SELECT +32768 FROM DUAL UNION ALL
  SELECT -32769 FROM DUAL;

Outputs:

VALUE INT_TO_2C_HEX
1000 03E8
-1000 FC18
-1 FFFF
1 0001
32767 7FFF
-32768 8000
32768 null
-32769 null

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions