2

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.

  • How are you getting -29440 from 0x00..008D00? – Aaron Bertrand Sep 10 '15 at 19:38
  • select MyField as foo,convert(binary,MyField) as bar – Jeff Sacksteder Sep 10 '15 at 19:40
  • That seems like the opposite. Can you show your table structure, and the two rows of data that are actually stored? I am confused about what is actually in your table, and what -29440 **or** 0x00...8D00 means. Also, you should specify the length of the binary conversion (e.g. binary(16)), and that is not the value I get when I convert(binary,-29440), either. – Aaron Bertrand Sep 10 '15 at 19:47
  • Also read Connect Item - UNSIGNED INTEGER Data Type – Kin Shah Sep 10 '15 at 19:48
  • 2
    If you've obfuscated such that we can't figure out how you got 27136 from that second hex value, then we either can't help you or we have to assume that some of the data was lost during the conversion process. – Aaron Bertrand Sep 10 '15 at 20:01
3

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.

1

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.

  • Yeah I think some data is missing. I can get to 141 easily with CONVERT(INT, 0x000000000000000000000000000000000000000000000000000000008D00/256) but I can't get to 362 from that other binary value. – Aaron Bertrand Sep 10 '15 at 19:59
  • The source system is no longer available. See comment above. I'm pretty sure all the bits from the source came over, but the representation is weird. It was a cobol app- who knows what it was doing in that field. – Jeff Sacksteder Sep 10 '15 at 20:01
  • I can get close with a bitwise AND and adding one. – Jeff Sacksteder Sep 10 '15 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.