CODEWITHSUNDEEP
sqlsql-servert-sqlbit-manipulationbitwise-operators
unj2
unj2

Reputation: 53491

How do I flip a bit using bitwise operator for an int in TSQL?

I have a flags column in sql which is an int.

I need to set a bit in the flag with a SQL statement. The corresponding C++ statement looks something like flags &= ~(unsigned long) (0x10000000).

I tried to do 

update [Databases]
 set flags = flags & ~0x10000000

but i get a 

Operand data type varbinary is invalid for '~' operator.

Then I tried to see what the convert function would yeild

select flagInt = CONVERT(int , 0x10000000)

gives me 268435456.

select flagIntInvert = ~CONVERT(int , 0x10000000)

gives me -268435457

which is the same as the signed int

flag    268435456   int
        ~flag   -268435457  int

However I need the unsigned versions

(uint32)flag    268435456   unsigned long
~(uint32)flag   4026531839  unsigned long

Is there a way to set the flag to be the inversion of 0x10000000?

Thanks.

Upvotes: 4

Views: 2756

Answers (1)

Janolof.
Janolof.

Reputation: 141

Setting the inversion of the flag can be used but applying the bitwise NOT operator ~. The int in SQL Server is an signed int.

From what I can see you want to active a bit in an integer and that can be done using the following statements.

Assume you are having an integer with value 1, i.e. the first bit is active.

DECLARE @Value int = 1;

And you want to active the third bit you use the or-operator (|) as below:

SET @Value = @Value | 4;

This will render the value 5 of the int variable.

SELECT @Value;

If you then want to check if the third bit is set in use the following, which will filter of all other active bits:

SELECT @Value & 4

And if you want to use it in comparing use the following statement:

IF @Value & 4 = 4 ...

Hope this helps.

Upvotes: 4

Related Questions