Converting Binary to Integer

Question

Converting Binary to Integer

Graham Macfarlane

Mr or Mrs. 500

Points: 539
More actions
September 9, 2005 at 1:35 am

#88962

I need to import 24 bit binary values (eg: 010000000010101111011101) as decimal (eg: 4205533) for a new database. I am storing the imported binary value in a BINARY variable and then converting it to Integer. The trouble is that CONVERT or CAST give me the wrong decimal value. I think it may be to do with the way SQL Server stores binary data (in chunks of HEX?) and I have also read something about it padding out the data. Has anyone come across this before?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 1

Try front-loading that 24-bit value with 8 zeros, converting it into a 32-bit binary, and then into an INT, before converting it into a DECIMAL.

On second thought, never mind. I think something else is going on. How are you setting that BINARY variable?

Graham Macfarlane Mr or Mrs. 500 Points: 539 More actions · Answer 2

Thanks. I have just tried the following

DECLARE @BinaryVariable binary (32)

SET @BinaryVariable = 00000000010000000010101111011101

SELECT @BinaryVariable

SELECT CAST( @BinaryVariable AS int)

The binary is stored as 0x00000000000000000000000000000000170000011D37628BEAD2E0191E020000 and when I cast this to int, I get 503447552 instead of the 4205533 I am after.

ziller SSC Eights! Points: 897 More actions · Answer 3

When you set the @binaryvariable, it is treating the string of 1's and 0's as a decimal, not a binary number. To initialize the var, use hex notation, then it should work. also, the length when declaring the binary var is in bytes, not bits, so it should be 3 (for 24 bits)

DECLARE @BinaryVariable binary (3)

SET @BinaryVariable = 0x402BDD

SELECT @BinaryVariable

SELECT CAST( @BinaryVariable AS int)

this will give you the correct result. SO, you need to write something to convert the string of 1's and 0's to hex. Maybe convert to string and parse to create a hex number...

Here is an example of how it is treating a string of 1's and 0's as a decimal, not a binary string:

declare @x binary(3)

set @x = 1101

print @x

select convert(int, @x)

results:

0x00044D

-----------

1101

(1 row(s) affected)

hope this helps...

Graham Macfarlane Mr or Mrs. 500 Points: 539 More actions · Answer 4

Thanks very much. I had an inkling that I might end up having to convert the 1's and 0's to the hex format that the SQL Binary data type understands, so this has confirmed it. I'll look at finding a way converting the whole file to Hex and prefixing each row with 0x before importing it and converting to integer.