August 19, 2009 at 12:06 pm
I'm looking for the single line statement that will explicitly set a specific bit position in an integer field to the bit value (i.e., 0 or 1) of a bit parameter and leave all other bits unchanged.
I know that that I can test the value of the bit parameter and then use an IF..THEN..ELSE with a bitwise OR (|) to explicitly turn it on and a bitwise AND NOT (& ~) to explicitly turn it off. But I'm looking for something that will not require an IF statement.
The bit to be set is known so providing a constant as part of the equation is not a problem.
Example values modifying the fourth bit:
DECLARE @i int;
DECLARE @b-2 bit;
SET @i = {logic here} equivalent to: SET @i = @i + @b-2
@i @b-2 Desired result
0 0 0
0 1 8
23 0 23
23 1 31
31 0 23
31 1 31
August 19, 2009 at 1:20 pm
I have to ask, is there any reason to not simply add 8 * @b-2 to @i?
declare @i int, @b-2 bit;
select @i = 0, @b-2 = 1;
select @i + (@b * 8);
Seems to do what you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 1:30 pm
GSquared (8/19/2009)
I have to ask, is there any reason to not simply add 8 * @b-2 to @i?
declare @i int, @b-2 bit;select @i = 0, @b-2 = 1;
select @i + (@b * 8);
Seems to do what you're looking for.
It doesn't match my desired results. I.e., it doesn't work all of the time. E.g., if the current value is 31 (bit 4 is already on) and a value of 1 (true) is passed in, the result will be 39 and not 31. In this case, the bit setting should essentially be a NO-OP.
Conversely, if the current value is 31 (bit 4 is already on) and a value of 0 (false) is passed in, the result will be 31 and not 23.
August 19, 2009 at 2:33 pm
How about the following formula?
DECLARE @bitPos TINYINT
SET @bitPos = 4 -- EDIT: was 3
SELECT @i - ( ( FLOOR(@i/(POWER(2,(@bitPos-1)))) % 2 - @b-2) * (POWER(2,(@bitPos-1))))
Some explanation:
Step 1:
@bitPos is used to be able to modify any requested bit position (please note that there is no test for valid data, e.g. negative values...)
Step 2:
POWER(2,(@bitPos-1)) will return the integer value of the @bitPos variable (8 in the given example for bit position 4) [EDIT]: see note
Step 3:
FLOOR(@i/POWER(2,(@bitPos-1))) will give you the BIT pattern starting with the bit matching @bitPos of the binary structure (4 in this case). It actually eliminates bit 1 to 3 of @i.
Example:
FLOOR (24/8) will return 3, which is 0011 in binary format.
FLOOR (17/8) will return 2, which is 0010 in binary format.
Step 4:
%2 will eliminate every bit except the first one, leaving 1 for @i = 24 and 0 for @i = 17.
Step 5:
subtracting @b-2 will result in 0, if the bit in question is already at the requested state, -1 if it needs to be turned from 0 to 1 and 1 if it needs to be turned from 1 to 0.
Step 6:
Multiply the result by the integer value represented by the bit position @bitPos. This is to respect that we're working on a specific bit (bit 4 in this case).
Step 7:
Modify @i:
Turning the bit from 0 to 1 means add 8, turn it from 1 to 0 means subtract 8.
Edit: bad mistake: bit position is NOT equal to the power value (explanation didn't match code provided). Function and description modified... :blush:
August 19, 2009 at 5:10 pm
noone has mentioned the TSQL bitwise operators yet. pipe (|)and carat(^) for flipping bits, and ampersand for comparisons(&)
here's an example for you all:
declare @myBitField int
SET @myBitField = 42 --meaning of life
--bit value 3 = True,
--bit value 5 = True, all others are false
--turn on bit 12
--first line to uncomment to test
--SET @myBitField = @myBitField | 4096 --pipe character is bitwise OR
--turn Off bit 3
--second line to uncomment to test
--SET @myBitField = @myBitField ^ 8
--SELECT '@myBitField: ' + CHAR(13) +
'bit value 1 '+ MIN(CASE @myBitField & 1 WHEN 1 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 2 '+ MIN(CASE @myBitField & 4 WHEN 4 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 3 '+ MIN(CASE @myBitField & 8 WHEN 8 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 4 '+ MIN(CASE @myBitField & 16 WHEN 16 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 5 '+ MIN(CASE @myBitField & 32 WHEN 32 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 6 '+ MIN(CASE @myBitField & 64 WHEN 64 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 7 '+ MIN(CASE @myBitField & 128 WHEN 128 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 8 '+ MIN(CASE @myBitField & 256 WHEN 256 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 9 '+ MIN(CASE @myBitField & 512 WHEN 512 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 10 '+ MIN(CASE @myBitField & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 11 '+ MIN(CASE @myBitField & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 12 '+ MIN(CASE @myBitField & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 13 '+ MIN(CASE @myBitField & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 14 '+ MIN(CASE @myBitField & 4194304 WHEN 4194304 THEN 'True' ELSE 'False' END) + CHAR(13) +
'bit value 15 '+ MIN(CASE @myBitField & 1073741824 WHEN 1073741824 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(13)
Lowell
August 19, 2009 at 11:51 pm
Lowell (8/19/2009)
noone has mentioned the TSQL bitwise operators yet. pipe (|)and carat(^) for flipping bits, and ampersand for comparisons(&)
The OP mentioned it in the first post:
JohnG (8/19/2009)
I know that that I can test the value of the bit parameter and then use an IF..THEN..ELSE with a bitwise OR (|) to explicitly turn it on and a bitwise AND NOT (& ~) to explicitly turn it off. But I'm looking for something that will not require an IF statement.
The sample you provided still requires a test whether the bit needs to be turned on or off, leading to two different statements (one with pipe and the other one with carat). Those two statements can be wrapped in a CASE condition, meeting the OP's requirement (no IF condition). 😉
What I was trying to do is to come up with a single statement that doesn't need to test for the bit value.
August 20, 2009 at 2:28 am
How about this
(@i & 0xFFFFFFF7) ^ (@b * 8)
Far away is close at hand in the images of elsewhere.
Anon.
August 20, 2009 at 5:24 am
Thanks Lutz;
I was under the impression he didn't have a handle on the bit operator yet.
your example toggles the bit, right? mine was lamer and tries to set the bit, and it may already be set, so would have no effect.
I thought if he knows which specific bit he wants to turn on or off, he can use my examples like this:
--turn on bit 12
SET @myBitField = @myBitField | 4096 --pipe character is bitwise OR
--turn Off bit 3
--SET @myBitField = @myBitField ^ 8
if he was using a function or something, to pass which bit, ie 1 thru 15, i was thinking maybe something like this:
declare @WhichBitToFlip int,@OnOrOff int
SET @WhichBitToFlip =12
SET @OnOrOff = 1--True/On
--turn on bit 12
If @OnOrOff != 0
SET @myBitField = @myBitField | POWER(2,@WhichBitToFlip ) --4086
Else
SET @myBitField = @myBitField ^ POWER(2,@WhichBitToFlip ) --4086
Lowell
August 20, 2009 at 5:41 am
VERY COOL David!
August 20, 2009 at 7:47 am
Thanks to all of the contributors to my post.
Lowell (8/19/2009)
noone has mentioned the TSQL bitwise operators yet. pipe (|)and carat(^) for flipping bits, and ampersand for comparisons(&)
Yes, I DO have a handle on all of the bitwise operators. I was just looking for an elegant, single statement, method to replace the following:
[font="Tahoma"]UPDATE MyTable SET
PropertyMask =
CASE @BitValue
WHEN 0 THEN PropertyMask & ~ 8
WHEN 1 THEN PropertyMask | 8
END
WHERE ...[/font]
Although the above code works fine, I figured that there was some algorithm just beyond my mental grasp that would do it with a single statement. It was more of a "technical challenge" than a blocking problem.
lmu92 (8/19/2009)
How about the following formula?
DECLARE @bitPos TINYINTSET @bitPos = 4 -- EDIT: was 3
SELECT @i - ( ( FLOOR(@i/(POWER(2,(@bitPos-1)))) % 2 - @b-2) * (POWER(2,(@bitPos-1))))
Lutz,
Although it solves the problem, having to provide a page of code comments (i.e., documentation) in the stored procedure so that the next developer can understand what is happening is a deal-breaker.
David Burrows (8/20/2009)
How about this(@i & 0xFFFFFFF7) ^ (@b * 8)
David,
That is exactly what I was looking for. I.e., some combination of bitwise operands. However, instead of providing 0xFFFFFFF7 as a constant for the 4th bit, one can use ~ 8. So the formula becomes:
[font="Arial Black"](@i & ~ 8) ^ (@b * 8)[/font]
Note: There is a bug in the SQLServerCentral code formatter. The bitwise NOT operator ~ (tilde) in blocks appears as ' (single quote). Hence the nonuse of blocks in my examples.
August 20, 2009 at 8:13 am
John,
you could leave out the comment for the next developer. It's pretty much self explaining, isn't it? 😉
I, personally, would have added much less explanation in a procedure or documentation since I'd expect that somebody who needs to be familiar with the complete business case should also be familiar with the methods I used...
However, I have to admit: Davids solution is much more cleaner. But I wouldn't leave in a code without any explanation either. How long that documentation is, is up to you...
Again: congrats to David. Good job!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply