Bit-flipping operations in T-SQL

Question

I have a bitmasked int field in my database. Usually I manage it through C# code, but now I need to flip a bit in the mask using T-SQL

How do I accomplish the following:

The bit I want to flip: 1 << 8 (256)

The mask value before I flip: 143

The mask value after I flip: 399

This can be done without the bit operators that's missing in T-SQL, right?

score 25 · Accepted Answer · answered Jul 10 '09 at 15:17

25

Use XOR:

SELECT value ^ 256

So in your case, SELECT 143 ^ 256 will indeed return 399. If you want to pass in the exponent as well:

SELECT value ^ POWER(2, power)

answered Jul 10 '09 at 15:17

David M

thanks! It solved my problem! Is there also a way to turn on the bit in an elegant way? (i already did it, but it looks ugly...) – Christian Dalager Jul 10 '09 at 15:29
2

Are you looking to flip or turn on? Flip - use XOR (^); turn on - use OR (|). If you want more elegance, wrap in a tersely named user-defined function. :) – David M Jul 10 '09 at 15:31

score 20 · Answer 2 · answered Jul 10 '09 at 15:20

20

TSql Bitwise operators can be found here and good article on how to use them is here

answered Jul 10 '09 at 15:20

Charles Bretana

1

The question is about bit shifting, the articles you posted have nothing to do with that – Edwin Dec 14 '16 at 08:42
4

The question is about bit-FLIPPING, not about bit shifting. – Charles Bretana Dec 14 '16 at 13:22
The title is wrongly about bit shifting, but the actual question about bit flipping. – Reversed Engineer Jul 18 '17 at 14:39

2 Answers2