February 11, 2010 at 7:13 am
Hi all,
I have a BIGINT where the first 8 bits contain one number the next 28 bits contain a second number and the final 28 bits contain the values for a third number.
Has anyone any idea how to unpack it into it's constituent parts.
Thanks
Allen
February 11, 2010 at 11:05 am
I would go about it like this:
DECLARE @myTest bigint
SET @myTest = 9223372036854775807
SELECT @myTest & CAST(9151314442816847872 AS bigint)/
CAST(72057594037927936 AS bigint) Value1,
@myTest & CAST(72057593769492480 AS bigint)/
CAST(268435456 AS bigint) Value2,
@myTest & CAST(268435455 AS bigint) Value3
I've given @myTest here the largest possible value - 1 of the 64 bits is used to indicate plus/minus value.
I've used a bitwise and to mask out all but the relevant numbers so that for example to get the 9 to 36 bits I anded with 0xFFFFFFF0000000 which is 72057593769492480, then divide by the correct value so that we get units. I bet there's a better way of doing this though - anyone?
February 11, 2010 at 11:21 am
Thank you!
I knew about bitwise and but had only used it to test for a series of flags stored in an integer (1 = a , 2 = b, 4 = c so 5 = a and c). I didn't realise it could be used for this. Also would never have got the divide to get to the correct units.
PS Which bit is used for the sign?
Thanks again
Allen
February 11, 2010 at 11:25 am
I don't have a solution at this time, but had the 2nd and 3rd values been byte aligned you could have used SUBSTRING by casting the BIGINT value as a BINARY(8). Unfortunately, with those values using 28 bits each, this won't work.
February 11, 2010 at 11:32 am
Just wondering why you & with 9223372036854775807 not 9223372036854775808 .
I got 9223372036854775808 by 2^63, so I'm missing something.
February 12, 2010 at 2:13 am
Doh I forgot to take off 1 for storing zero.
February 12, 2010 at 4:18 am
Hi
I'm still struggling, I don't understand how to calculate the divisor to get to the right units. Below is wrong but I cant see why..
DECLARE @myTest tinyint
-- v1 left 2 bits xx000000
-- v2 middle 3 bits 00xxx000
-- v3 right 3 bits 00000xxx
-- get something in each value
-- 10101011 = 171
SET @myTest = 171
-- v1 = 10 = 2
-- v2 = 101 = 5
-- v3 = 011 = 3
-- get v1 mask using 11000000 = 192 / 1000000 = 64
SELECT @myTEST & CAST(192 AS tinyint)/ CAST(64 AS tinyint)
-- get v2 mask using 111000 = 56 / 1000 = 8
SELECT @myTEST & CAST(56 AS tinyint)/ CAST(8 AS tinyint)
-- v3 mask using 111 = 7
SELECT @myTEST & CAST(7 AS tinyint)
February 12, 2010 at 4:44 am
allen davidson (2/12/2010)
HiI'm still struggling, I don't understand how to calculate the divisor to get to the right units. Below is wrong but I cant see why..
Allen,
there's nothing wrong with your divisor. It's a matter of operator precedence. Try this:
DECLARE @myTest tinyint
-- v1 left 2 bits xx000000
-- v2 middle 3 bits 00xxx000
-- v3 right 3 bits 00000xxx
-- get something in each value
-- 10101011 = 171
SET @myTest = 171
-- v1 = 10 = 2
-- v2 = 101 = 5
-- v3 = 011 = 3
-- mask using 11000000 = 192 / 1000000 = 64
SELECT (@myTEST & CAST(192 AS tinyint))/ CAST(64 AS tinyint)
-- mask using 111000 = 56 / 1000 = 8
SELECT (@myTEST & CAST(56 AS tinyint))/ CAST(8 AS tinyint)
-- mask using 111 = 7
SELECT @myTEST & CAST(7 AS tinyint)
February 12, 2010 at 4:59 am
Thanks Willem, as usual I didn't see the error because I was looking in the wrong place!
Cheers
Allen
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply