Updating a bit? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating a bit?

Author

Topic

Robindude
Starting Member

1 Post

Posted - 2011-02-16 : 13:51:30

I've got a column that uses an INT value to store various flag bits. I'm having some trouble with things related to one of these flags, so I want to turn them all off. Obviously this means reducing the value of the int by X (524288 in this case, or 2^19) if that bit is set, otherwise leaving it alone. How would I go about doing this? I assume it's an UPDATE query, but I'm stuck as for how to turn just that bit to a 0 when it might be either 0 or 1 for a number of fields.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-16 : 14:13:25

If you want to turn them all off, set it to 0.

524288 isn't a valid value for a bitmask. If that's really what's in there, you're data is bad.
524287 means 19 bits, all on.

Use the bitwise & operator to see if specific bits are on or off.

Valid #s are the following, plus any combination of the sum of any or all of them

1
2
4
8
16
32
64
128
256
512
1024
2048
4096
8192
16384
32768
65536
131072
262144

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-16 : 15:11:57

I take it back...I only did 2^18

524288 means that only the 19th bit is on

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-16 : 17:02:09

Something like this?

DECLARE @t TABLE(Mask INT)

INSERT @T
SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 8
UNION SELECT 10
UNION SELECT 16
UNION SELECT 524288

-- Where BIT 19 is NOT set
SELECT *
FROM @t
WHERE Mask & POWER(2, 19) = 0

-- SET BIT 19
UPDATE @T 
SET Mask = Mask | POWER(2, 19)
WHERE Mask & POWER(2, 19) = 0

-- Results
SELECT * FROM @T

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-16 : 17:12:53

Opps I think I read your question backwards:

DECLARE @t TABLE(Mask INT)

INSERT @T
SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 8
UNION SELECT 10
UNION SELECT 16
UNION SELECT 524288

-- Where BIT 19 is set
SELECT *
FROM @t
WHERE Mask & POWER(2, 19) <> 0


-- Clear BIT 19
--Using Math
--UPDATE @T 
--SET Mask = Mask - POWER(2, 19)
--WHERE Mask & POWER(2, 19) <> 0

--Using XOR
UPDATE @T 
SET Mask = Mask ^ POWER(2, 19)
WHERE Mask & POWER(2, 19) <> 0

-- Results
SELECT * FROM @T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources