XML FeedsThis question came up today.
in c# i have a:
a = !a
(if false makes it true, if true makes it false)in sql i want to do the same with a BIT variable
In a language like C# you can use ! for a bool to make it become true if it was false and vice versa
So if you run this
- bool a = true;
- a = !a;
- Console.WriteLine(a.ToString());
- a = !a;
- Console.WriteLine(a.ToString());
- Console.ReadLine();
The output will be
False
True
How can you do this in SQL Server? It is pretty easy, take a look
- select ~ CONVERT(bit,0)
- select ~ CONVERT(bit,1)
The ~ symbol is the Bitwise NOT operator, here is what books on line has to say about the Bitwise NOT operator.
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.
Let's take a look at another example. What do you think will happen here?
- select ~ CONVERT(tinyint,0)
- select ~ CONVERT(tinyint,1)
That actually returns
255
254
Why is that? Here is a simple explanation
With a tinyint possible values are between 0 and 255, when you have 0 all bits are turned off, when you flip it, you turn all bits on and you get 255
00000000 = 0 ( 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0) 11111111 = 255 (1 + 2 + 4 + 8 + 16 + 32 + 64 + 128)
When you have 1 all bits are turned off except for the first bit, when you flip it you turn all bits on except for the first bit
00000001 = 1 ( 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0) 11111110 = 254 (0 + 2 + 4 + 8 + 16 + 32 + 64 + 128)
If you want to use tinyint or int you can use the following code
- select CONVERT(tinyint,0) ^ 1 as [tinyint], 0^1 as [int]
- select CONVERT(tinyint,1) ^ 1 as [tinyint], 1^1 as [int]
The ^ operator is the Bitwise Exclusive OR operator. Here is what books on line has to say about Bitwise Exclusive OR
The ^ bitwise operator performs a bitwise logical exclusive OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either (but not both) bits (for the current bit being resolved) in the input expressions have a value of 1. If both bits are 0 or both bits are 1, the bit in the result is cleared to a value of 0.
Let's take a closer look
The bits in the result are set to 1 if either (but not both) bits (for the current bit being resolved) in the input expressions have a value of 1.
0 ^ 1
00000000
00000001
--------
00000001
If both bits are 0 or both bits are 1, the bit in the result is cleared to a value of 0.
1^1
00000001
00000001
--------
00000000
So in the end if you want to flip a bit use the Bitwise NOT operator
- select ~ CONVERT(bit,0)
- select ~ CONVERT(bit,1)
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
If the output is going right back into a bit column you can just do `1 - BitCol` and it will work fine, too. 
Nice...I was waiting for your comment :-)
I should have posted it too as I answered this same question twice (using bitwise XOR operator). The second time I had to dig for my original answer as I forgot how I solved it
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.