Is there a way to convert either HEX or RAW value to signed 2's complement via Oracle SQL?
example:
hex: 8508 > dec: 34046 > dec from signed 2's complement: -31480
The number that I am trying to get within my SQL statement is -31480.
5
-
Arbitrary word size or 16 bit as per your example?– Jim DCommented Nov 20, 2020 at 2:21
-
hex: 8508 > dec: 34056 according to ncalculators.com/digital-computation/hex-decimal-converter.htm . 34056-65536=-31480, 65536 is power(2,16)– miracle173Commented Nov 20, 2020 at 7:51
-
@miracle173: your algorithm won't work. If you use hex 0000, your algorithm yields -65536 instead of the correct answer of 0.– Jim DCommented Nov 20, 2020 at 10:11
-
@BustedSanta I think your interpretation is strange: if you interpret 8508 as signed 16 bit number, then it is a negative number, not a positive one. The singed 16 bit numbers are the decimals between −32768 and +32767. 34056 is not in this interval– miracle173Commented Nov 20, 2020 at 16:17
-
@miracle173 your interpretation is correct. I believe BustedSanta was trying to show an intermediate step as an unsigned integer, which is not really relevant in this case as there is no need to convert to base 10 to do the conversion. I think hex, octal, or binary notation is best for these types of problems because the mapping to individual bits is clear (e.g. the sign bit in this case). I would have preferred to express the mask value in my answer as hex, but Oracle (AFAIK) does not allow hex literals.– Jim DCommented Nov 20, 2020 at 18:34
Add a comment
|
1 Answer
Using the expression
result = -(x & mask) + (x & !mask)
where mask=2^(n-1) yields the result you want. The (x & mask) isolates the sign bit and (x & !mask) is the offset. When the sign bit is not set, (x & mask) == 0 and (x & !mask) is the result. When the sign bit is set, -(x & mask) == -2^(n-1) and (x & !mask) is the offset from the most negative number.
Next we convert the hexadecimal string to a raw representation by using HEXTORAW(). The raw representation is cast to an integer with UTL_RAW.CAST_TO_BINARY_INTEGER().
Assuming 16 bit:
select
x,
-BITAND(utl_raw.cast_to_binary_integer(hextoraw(x)),32768)
+BITAND(utl_raw.cast_to_binary_integer(hextoraw(x)),32767)
from (select '8508' x from dual);
Note: You probably want to do the utl_raw.cast_to_binary_integer(hextoraw(...)) once instead of twice.
