CODEWITHSUNDEEP
sqlsql-serverbit-manipulation
Even Mien
Even Mien

Reputation: 45898

How do I flip a bit in SQL Server?

I'm trying to perform a bitwise NOT in SQL Server. I'd like to do something like this:

update foo
set Sync = NOT @IsNew

Note: I started writing this and found out the answer to my own question before I finished. I still wanted to share with the community, since this piece of documentation was lacking on MSDN (until I added it to the Community Content there, too).

Upvotes: 91

Views: 33302

Answers (5)

vitik
vitik

Reputation: 21

~ operator will work only with BIT,

try: ~ CAST(@IsNew AS BIT)

Upvotes: 2

Even Mien
Even Mien

Reputation: 45898

Bitwise NOT: ~

Bitwise AND: &

Bitwise OR: |

Bitwise XOR: ^

Upvotes: 35

Blorgbeard
Blorgbeard

Reputation: 103467

Lacking on MSDN? http://msdn.microsoft.com/en-us/library/ms173468(SQL.90).aspx

~: Performs a bitwise logical NOT operation on an integer value. The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.

Upvotes: 12

Oliver
Oliver

Reputation: 3255

For the sake of completeness:

SELECT b, 1 - b
FROM
  (SELECT cast(1 AS BIT) AS b
   UNION ALL
   SELECT cast(0 AS BIT) AS b) sampletable

Upvotes: 2

Jason Kresowaty
Jason Kresowaty

Reputation: 16500

Yes, the ~ operator will work.

update foo
set Sync = ~@IsNew

Upvotes: 137

Related Questions