How to flip bit fields in T-SQL?

Question

I'm trying to flip a bit in SQL Server using an update request, that is, I want to do all 0 in 1 and vice versa. What is the most elegant solution?

There seems to be no bitwise NOT operator in T-SQL (unless I see something obvious), and I could not find another way to perform the update.

Answer 1

You do not need bitwise - not for this - just XOR it with 1 / true.

To check this:

select idColumn, bitFieldY, bitFieldY ^ 1 as Toggled from tableX 

To update:

 update tableX set bitFieldY = bitFieldY ^ 1 where ... 

MSDN T-SQL Exclusive-OR (^)

Answer 2

Why not just bitfield = 1 - bitfield ?

Answer 3

Another way -

 DECLARE @thebit bit = 1, @theflipbit bit SET @theflipbit = ~ @thebit SELECT @theflipbit 

where "~" means the operator "NOT". It is clean and you get good code to read. “deny bit" is even cleaner, and it does exactly what the NOT operator was designed for.

Answer 4

I was sure that most of the SQL versions were bitwise, so I checked and there seems to be one of TSQL .

From the documentation, this is the ~ character.

Answer 5

 UPDATE tblTest SET MyBitField = CASE WHEN MyBitField = 1 THEN 0 ELSE 1 END 

This is mild, but everyone will understand what he is doing.

EDIT:

You may also need to consider zeros, as suggested in the comments. Depends on your requirement, 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 

Answer 6

The simple bitwise NOT (~) operator worked for me in SQL Server 2014 - 12.0.2269.0

In the update clause inside your 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

Answer 7

Have you tried this?

 UPDATE mytable SET somecolumn = CASE WHEN somecolumn = 0 THEN 1 WHEN somecolumn IS NULL THEN NULL WHEN somecolumn = 1 THEN 0 END 

Answer 8

query (vb)

 x = "select x from table" 

update (vb)

 "update table set x=" Not(x*(1))