Sorry to jump in here Jeff, but I just love bitmasks. I so rarely get to do anything that low level.
To retrieve a single bit from a binary number you need to use a bitmask and to shift the result. You have a binary number, say: 1101010100. You want to see whether column 5 was updated. So you AND it with a bitmask, which is a binary number with all zeros except for the bit you want to get:
Code:
1101010100 &
0000010000
=
0000010000
This isn't much use because you don't get a 1 or 0 back. So you need to right shift the result. Right shifting one place is effectively the same as dividing by 2. So you want to divide this result by 2 4 times (ie divide by 2^5) to shift it right 4 times. The end result is to return that value 1.
Putting this into something more useful and general, try the following code to check to see whether a bit in a number was switched high or low:
Code:
DECLARE @Cols AS VarBinary(20), @Column AS Int, @Result AS bit, @Mask AS int, @MaskRes AS VarBinary(20)
-- set the column mask
SET @Cols = 0xDA -- 11011010
-- set the column to check for
SET @Column = 5
-- build the mask
SET @Mask = POWER(2, @Column - 1)
-- apply the mask
SET @MaskRes = @Cols & @Mask
-- shift the result
SET @Result = CAST( @MaskRes / POWER(2, @Column - 1) AS bit)
PRINT @Result
I split the code out into multiple steps to make it clear what is happening in each step. You could also simplify the last step by simply casting the result of the bitmask to bit, but it feels a little untidy to me.
A few comments on working with binary numbers in SQL Server.
1. You can't assign a binary number to a variable, or at least I haven't managed to yet. So stick to hex. *Do Not* use decimal. The conversion between hex and binary is relatively simple (4 bits = 1 hex symbol), the conversion between decimal and binary involves a calculator if the numbers are more than about 8 bits or so.
2. The & operator can only take one binary/varbinary parameter.
3. Using windows' calculator in scientific mode. Can be very handy.
4. The least signifigant bit is at the far right, and it also called the zeroth bit. Remember to start counting from zero. You'd notice in the code above when I used POWER I used 2^4, rather than 2^5. This is why.
5. Terminonlogy, Setting a bit = set it to one, clearing a bit = set to zero. high = 1, low = 0 (from digital logic circuit design).
Some generally useful binary rules:
1. To retrieve a bit, use a bitmask of zeroes with a 1 for the position you want to get, AND the mask and the number then right shift.
2. To clear a bit, use a bitmask of ones with a zero for the position you want to clear then AND the number and the mask.
3. To set a bit, use a bitmask of zeroes with a one for the position you want to clear then AND the number and the mask.
regards
David Cameron
Columns_Updated
Back in the real old days when I cut my computing teeth I really did use punch cards. I recall many a late night at a keypunch machine. I remember one time when I dropped the compiler. Really. All over the floor. My whole life flashed before my eyes as 3500 cards flew all over the room. Quite a chore getting them back together. I learned to always use a card tray after that...