How to convert unsigned int accidentally stored as signed int?

Question

During a data conversion process, a field that was apparently an unsigned int was stored in a Sql Server int field(which is signed).

To remedy this apparently requires some combination of bitwise operators and type conversion that I'm struggling with.

For example the value 141 is stored as

-29440 or 0x000000000000000000000000000000000000000000000000000000008D00

and the value 362 is stored as

27136 or 0x000000000000000000000000000000000000000000000000000000006A00

Presumably I need to convert to binary first to thwart any implied casting.

Answer 1

A smallint in SQL Server is stored as a signed int16, or 2 bytes, or 16 bits with the 16th bit reserved to indicate the sign (0 = positive, 1 = negative).

Here are your two examples, converted to the original un-signed int (uint), and int16 values: It looks like in your examples that your bits have been shifted to the left by 8 bits. Noticed the bolded items below are the same.

Original Value 1: uint = 141 = 0x008D = 00000000 10001101
Was converted to: int16 = -29440 = 0x8D00 = 10001101 00000000

Original Value 2: uint = 362 = 0x016A = 00000001 01101010
Was converted to: int16 = 27136 = 0x6A00 = 01101010 00000000

Unfortunately, in your second scenario (the conversion from 362 that resulted in 27136), the 1 in the 9th bit position (00000001 01101010) has been dropped during your data conversion.

It appears your conversion process was something like:

1. Shift the original # to the left by 8 bits (this will add 8 bits of 0 padding added on the right-hand side),
2. Convert to smallint (16 bit data type), in a way that truncated the 8 left-most bits.

In this case, then any # greater than 256 in your original dataset would have been converted to a # that represents only the 8 right-most bits with 8 bits of 0 padding adding on the right, and you will not be able to recover any number from your original data that was above 256 because it looks like the left-most 8 bits have been truncated during the failed data conversion to a 16 bit data type.

For example, given the # 27136 (01101010 00000000) in your current data, you cannot know if this original # was 106 (00000000 01101010) or 362 (00000001 01101010) or 874 (00000011 01101010), or any # that originally had a 1 within the position of bits 9-16.

Answer 2

I think you might be mistaken about what happened to your data. Positive integers generally have the same representation as signed and unsigned integers.

It looks like your first value is 564 or 141 * 4. The second value looks like 424, or (362 - 256) * 4. It's possible that your integer was stored as a byte (truncating it to 0-255) and then bit-shifted, to multiply it by 4. It's also possible that you are retrieving or interpreting the bits incorrectly.

Why don't you rewrite the data correctly? Is this your only copy? If so, and if it is being truncated, you might have a problem since it doesn't look like you are going to be able to recover all the original values.