如何翻转 T-SQL 中的位字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1397332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to flip bit fields in T-SQL?
提问by Billious
I'm trying to flip a bit field in SQL Server using an update query, that is, I want to make all the 0's into 1's and vice versa. What's the most elegant solution?
我正在尝试使用更新查询翻转 SQL Server 中的一个位字段，也就是说，我想将所有 0 变为 1，反之亦然。什么是最优雅的解决方案？
There doesn't seem to be a bitwise NOT operator in T-SQL (unless I'm missing something obvious) and I haven't been able to find any other way of performing the update.
T-SQL 中似乎没有按位 NOT 运算符（除非我遗漏了一些明显的东西），而且我找不到任何其他执行更新的方法。
回答by Austin Salonen
You don't need a bitwise-not for this -- just XOR it with 1 / true.
为此，您不需要按位 - 只需将其与 1 / true 进行异或即可。
To check it:
要检查它：
select idColumn, bitFieldY, bitFieldY ^ 1 as Toggled
from tableX
To update:
更新：
update tableX
set bitFieldY = bitFieldY ^ 1
where ...
回答by gbn
Why not a simple bitfield = 1 - bitfield?
为什么不简单bitfield = 1 - bitfield？
回答by Leonardo Marques de Souza
Another way is
另一种方式是
DECLARE @thebit bit = 1, @theflipbit bit
SET @theflipbit = ~ @thebit
SELECT @theflipbit
where "~" means "NOT" operator. It's clean and you get a good code to read. "negate the bit" is even cleaner and it does exactly what the "NOT" operator was designed for.
其中“~”表示“NOT”运算符。它很干净，你会得到一个很好的代码来阅读。“否定位”甚至更清晰，它完全符合“NOT”运算符的设计目的。
回答by Thomas Owens
I was pretty sure that most SQL flavors had a bitwise NOT, so I checked and there does appear to be one in TSQL.
我很确定大多数 SQL 风格都有一个按位 NOT，所以我检查了 TSQL中似乎确实有一个。
From the documentation, it's the character ~.
从文档中，它是字符~.
回答by Mayo
UPDATE tblTest SET MyBitField = CASE WHEN MyBitField = 1 THEN 0 ELSE 1 END
It's bland but everyone will understand what it's doing.
这很乏味，但每个人都会明白它在做什么。
EDIT:
编辑：
You might also need to account for nulls as suggested in the comments. Depends on your req's of course.
您可能还需要按照评论中的建议考虑空值。当然取决于你的要求。
UPDATE tblTest SET
MyBitField = CASE
WHEN MyBitField = 1 THEN 0
WHEN MyBitField = 0 THEN 1
ELSE NULL -- or 1 or 0 depending on requirements
END
回答by Saksham Gupta
A simple bitwise NOT operator (~) worked for me in SQL Server 2014 - 12.0.2269.0
一个简单的按位非运算符 (~) 在 SQL Server 2014 - 12.0.2269.0 中对我来说有效
In the update clause inside your T-SQL -
在 T-SQL 中的更新子句中 -
Update TableName
SET [bitColumnName] = ~[bitColumnName],
....
WHERE ....
Hope this helps
希望这可以帮助
Ref - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-not-transact-sql
参考 - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-not-transact-sql
回答by Nasa Rahman
query (vb)
查询 (vb)
x = "select x from table"
update (vb)
更新 (vb)
"update table set x=" Not(x*(1))
回答by eKek0
Did you try this?
你试过这个吗？
UPDATE mytable SET somecolumn =
CASE WHEN somecolumn = 0 THEN 1
WHEN somecolumn IS NULL THEN NULL
WHEN somecolumn = 1 THEN 0
END
