Oracle十六进制数字转换双字 [英] Oracle hex to number conversion dword

问题描述

我正在尝试将十六进制转换为数字.如果我使用

I am trying to convert hex to number. if I use

SELECT To_Number('F8EF7F2C', 'xxxxxxxx')  FROM dual;

结果是4176445228.这是qword结果.

The result is 4176445228. This is qword result.

我想要得到的是-118522068的dword结果

What I want to get is dword result which -118522068

我该如何实现?

谢谢

更新:

SELECT to_number('F8EF7F2C', 'xxxxxxxxxx')-Power(2,32) FROM dual

似乎适用于此值'F8EF7F2C'.但是将十六进制值更改为

seemed to work for this value 'F8EF7F2C'. But changing the hex value to

SELECT to_number('0208A5FA', 'xxxxxxxxxx')-Power(2,32) FROM dual

产生错误的结果-4260846086.正确的结果是34121210，它是由

produces wrong result -4260846086. The correct result is 34121210 which is produced by

SELECT to_number('0208A5FA', 'xxxxxxxxxx') FROM dual

我可以在Dword中以编程器模式从PC计算器验证正确的结果.

I can verify the correct results from PC calculator in programmer mode in Dword.

推荐答案

据我所知，您的qword是无符号的32位整数，而dword是有符号的32位整数.

As I know the terms, your qword is an unsigned 32-bit integer, while dword is a signed 32-bit integer.

下面是一些十六进制值的测试:

Here's a little test with some hex values:

with hex as (
   select '00000000' hex from dual union all
   select '0208A5FA' hex from dual union all
   select '7FFFFFFF' hex from dual union all
   select '80000000' hex from dual union all
   select 'F8EF7F2C' hex from dual union all
   select 'FFFFFFFF' hex from dual
)
select hex.hex
     , to_number(hex.hex,'XXXXXXXX') unsigned
     , case
          when to_number(hex.hex,'XXXXXXXX') >= power(2,31)
          then to_number(hex.hex,'XXXXXXXX') - power(2,32)
          else to_number(hex.hex,'XXXXXXXX')
       end signed
     , mod(
          to_number(hex.hex,'XXXXXXXX') + power(2,31)
        , power(2,32)
       ) - power(2,31) signed_alternative
  from hex
 order by hex.hex
/

查询的输出是:

HEX        UNSIGNED        SIGNED SIGNED_ALTERNATIVE
-------- ---------- ------------- ------------------
00000000          0             0                  0
0208A5FA   34121210      34121210           34121210
7FFFFFFF 2147483647    2147483647         2147483647
80000000 2147483648   -2147483648        -2147483648
F8EF7F2C 4176445228    -118522068         -118522068
FFFFFFFF 4294967295            -1                 -1

列SIGNED的计算方式与@zerkms的注释中所建议的相同，CASE表达式仅用于在溢出时将2^32减去.

Column SIGNED is calculated like suggested in the comment by @zerkms, a CASE expression is used to only subtract 2^32 when it overflows.

列SIGNED_ALTERNATIVE给出了相同的结果，但是首先要加上2^31，然后取模2^32，然后再次减去2^31.

Column SIGNED_ALTERNATIVE gives same result, but does it by first adding 2^31, then taking the modulo 2^32, and then subtracting 2^31 again.

CASE表达式的效率可能要高一些，因为它对这些NUMBER值执行的数字运算较少(如果它是浮点运算，则模数版本可能会更快，因为它可以进行优化)进行位操作；-)

The CASE expression might be a teeny bit more efficient as it has less numeric operations to perform on these NUMBER values (had it been floating point operations, the modulo version might have been faster as it could have been optimized to bit operations ;-)

这篇关于Oracle十六进制数字转换双字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！