1

I am using below select statement to convert a positive number to hexadecimal

SQL>select to_char('2122699925', 'XXXXXXXXXXXXXXXX') from testable
Output : 7E85D495

But the TO_CHAR for negative number it is giving me output as #################

SQL>select to_char('-2122699925', 'XXXXXXXXXXXXXXXX') from testable
Output : #################

The required output should be 817A2B6B. Any thoughts?

Bussller
  • 1,961
  • 6
  • 36
  • 50
vishal agrawal
  • 45
  • 3

1 Answers1

0

Documentation says (Table 2-13 Number Format Elements), here

Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.

Restrictions:

This element accepts only positive values or 0. Negative values return an error.

.......

However I was able to find the below link which does the conversation for negative numbers. I had no intention to copy it but providing you the link if it helps you in any way,

Conversion of negative numbers to hexadecimal

Sujitmohanty30
  • 3,256
  • 2
  • 5
  • 23
  • Hello Sujitmohanty, the post was helpful and did solve my question but what does number 4294967296 represents in the below statement? 
     1  SELECT val,
  2           To_Char(
  3            Decode(
  4               Sign(val), -1 , 4294967296  - Abs(val),
  5                               Abs(val)
  6           ), 'XXXXXXXX') FROM
  7*   (SELECT -1000175007 val FROM dual)
    – vishal agrawal Aug 21 '20 at 09:56
  • Its nothing but POWER(16,8) . As I understood from the post depends on the number we want to convert to HEX i.e. `16` represents the hexadecimal and `8` depends .on what kind of hexadecimal we want to convert 2 digit hex or 3 digit hex or 8 digit hex and so on.. There is some gap in understanding for me though with the alternative approach. – Sujitmohanty30 Aug 21 '20 at 12:00