Home » SQL & PL/SQL » SQL & PL/SQL » To convert a -ve number to Hexadecimal
icon4.gif  To convert a -ve number to Hexadecimal [message #430768] Thu, 12 November 2009 05:21 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member
Hi All,

select to_char(123,'xxx') from dual

Result
-------
7b

if I try to convert a -ve number to Hexadecimal using the same query

select to_char(-123,'xxx') result from dual

Result
-------
####

Any help will be much grateful.

regards,
Nataraj

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430775 is a reply to message #430768] Thu, 12 November 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Database SQL Reference
Section Format Models
Subsection Number Format Elements

Quote:
X
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.
...

Regards
Michel

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430779 is a reply to message #430775] Thu, 12 November 2009 05:39 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member
Miche,

Is there any workaround for this??

thanks,
Nataraj

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430782 is a reply to message #430779] Thu, 12 November 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, if you can define what is the hexadecimal value of a negative number.

Regards
Michel

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430785 is a reply to message #430782] Thu, 12 November 2009 05:52 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member
In SAS tool they have a function called HEXw that will convert native floating-point representation

http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/win-fm-hex.htm

I need to make it in SQL.

regards,
Nataraj

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430790 is a reply to message #430785] Thu, 12 November 2009 06:05 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That page doesn't explain with what *convention* they convert it.

One usual convention is to use a sign bit for negative values, which could be done in SQL with :


SQL> SELECT val,
  2         To_Char(
  3          Decode(
  4             Sign(val), -1 , 65536 - Abs(val),
  5                             Abs(val)
  6         ), 'XXXX') FROM
  7  (SELECT ROWNUM - 10 val FROM dual
  8   CONNECT BY LEVEL < 20);

       VAL TO_CH
---------- -----
        -9  FFF7
        -8  FFF8
        -7  FFF9
        -6  FFFA
        -5  FFFB
        -4  FFFC
        -3  FFFD
        -2  FFFE
        -1  FFFF
         0     0
         1     1
         2     2
         3     3
         4     4
         5     5
         6     6
         7     7
         8     8
         9     9

19 rows selected.

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430791 is a reply to message #430785] Thu, 12 November 2009 06:06 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://mathforum.org/library/drmath/view/55998.html
SQL> SELECT val,
  2         To_Char(
  3          Decode(
  4             Sign(val), -1 , 65536 - Abs(val),
  5                             Abs(val)
  6         ), 'XXXX') FROM
  7  (SELECT ROWNUM - 10 val FROM dual
  8   CONNECT BY LEVEL < 20);


valid for numbers greater than -65536
But good effort i appreciate

[Updated on: Thu, 12 November 2009 06:13]

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430797 is a reply to message #430791] Thu, 12 November 2009 06:22 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks for the link

sriram. Smile

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430800 is a reply to message #430790] Thu, 12 November 2009 06:28 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member
ThomasG wrote on Thu, 12 November 2009 17:35
That page doesn't explain with what *convention* they convert it.

One usual convention is to use a sign bit for negative values, which could be done in SQL with :


SQL> SELECT val,
  2         To_Char(
  3          Decode(
  4             Sign(val), -1 , 65536 - Abs(val),
  5                             Abs(val)
  6         ), 'XXXX') FROM
  7  (SELECT ROWNUM - 10 val FROM dual
  8   CONNECT BY LEVEL < 20);


But I need to convert huge nummbers??
My Range
-1000175007 to 9999506679


regards,
Nataraj

[Updated on: Thu, 12 November 2009 06:30]

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430802 is a reply to message #430800] Thu, 12 November 2009 06:32 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
But I need to convert huge nummbers??
My Range
-1000175007 to 9999506679


does this include decimal numbers as well like 1.01 or -1.01 Smile

what is the size of the word as well (when you say you are going to convert to negative like 16bit,8 bit)

Thomas example converts for 16 bit word
for 8 bit word

SQL> SELECT val,
  2           To_Char(
  3            Decode(
  4               Sign(val), -1 , 256 - Abs(val),
  5                               Abs(val)
  6           ), 'XXXX') FROM
  7    (SELECT ROWNUM - 10 val FROM dual
  8     CONNECT BY LEVEL < 20);

       VAL TO_CH
---------- -----
        -9    F7
        -8    F8
        -7    F9
        -6    FA
        -5    FB
        -4    FC
        -3    FD
        -2    FE
        -1    FF
         0     0
         1     1

       VAL TO_CH
---------- -----
         2     2
         3     3
         4     4
         5     5
         6     6
         7     7
         8     8
         9     9

19 rows selected.

[Updated on: Thu, 12 November 2009 06:38]

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430807 is a reply to message #430802] Thu, 12 November 2009 06:38 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member
no there is decimal numbers in my system

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430808 is a reply to message #430807] Thu, 12 November 2009 06:41 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
what is the size of the word as well (when you say you are going to convert to negative like 16bit,8 bit)

[Updated on: Thu, 12 November 2009 06:41]

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430812 is a reply to message #430800] Thu, 12 November 2009 06:48 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
rtnataraj wrote on Thu, 12 November 2009 13:28
But I need to convert huge nummbers??
My Range
-1000175007 to 9999506679

So change 65536 (= 16^4 = POWER(16, 4) in Oracle) with greater number. The best value would be the length of used format mask. Currently used mask 'xxx' (three hexadecimal digits) would not be able to show it anyway. Nine hexadecimal digits will suffice for that range, eight will not for the high value: 
SQL> select power(16,9), power(16,9)-1000175007, power(16,9)-9999506679 from dual;

POWER(16,9) POWER(16,9)-1000175007 POWER(16,9)-9999506679
----------- ---------------------- ----------------------
 6.8719E+10             6.7719E+10             5.8720E+10

1 row selected.

SQL> select power(16,8), power(16,8)-1000175007, power(16,8)-9999506679 from dual;

POWER(16,8) POWER(16,8)-1000175007 POWER(16,8)-9999506679
----------- ---------------------- ----------------------
 4294967296             3294792289             -5.705E+09

1 row selected.

SQL>

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430814 is a reply to message #430812] Thu, 12 November 2009 06:54 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I think 32 bit word will suffice

SQL> ed
Wrote file afiedt.buf

  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)
SQL> /

       VAL TO_CHAR(D
---------- ---------
-1.000E+09  C4628A61

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430841 is a reply to message #430814] Thu, 12 November 2009 08:34 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

It has worked for me,

Thank u very much ThomasG, flyboy & ayush_anand


regards,
Nataraj

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430859 is a reply to message #430768] Thu, 12 November 2009 10:44 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Thank you for feedback.

I am just curious which boundary you chose. Because, when using POWER(16,8), there might be problem with data ambiguity. A slight modification of ayush_anand's solution leads to this: 
SQL> SELECT val,
  2         To_Char( Decode( Sign(val), -1 , 4294967296 - Abs(val), Abs(val) ),
  3                  'XXXXXXXX' ) val_hex FROM
  4     (SELECT -1000175007 val FROM dual union all
  5      SELECT 3294792289 FROM dual)
  6  /

       VAL VAL_HEX
---------- ---------
-1.000E+09  C4628A61
3294792289  C4628A61

2 rows selected.

SQL>
Now, C4628A61 may represent number -1000175007 or 3294792289 (both are valid according to your post). Using POWER(16,9) (using this seems more understandable than that "magic" number above, although both are the same) should be safe: 
SQL> SELECT val,
  2         To_Char( Decode( Sign(val), -1 , power(16,9) + val, val ),
  3                  'XXXXXXXXX' ) val_hex FROM
  4     (SELECT -1000175007 val FROM dual union all
  5      SELECT 67719301729 FROM dual)
  6  /

       VAL VAL_HEX
---------- ----------
-1.000E+09  FC4628A61
6.7719E+10  FC4628A61

2 rows selected.

SQL>
(I just get rid of using ABS, as it is unnecessary).

Report message to a moderator

Re: To convert a -ve number to Hexadecimal [message #430894 is a reply to message #430859] Thu, 12 November 2009 22:56 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks flyboy..good work

Report message to a moderator

Previous Topic: Incremental counter required
Next Topic: order by given order
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 05 03:53:56 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.