How to flip bit fields in T-SQL?

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?

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.

Answers


You don't need a bitwise-not for this -- just XOR it with 1 / true.

To check it:

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

To update:

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

MSDN T-SQL Exclusive-OR (^)


Why not a simple bitfield = 1 - bitfield?


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.


I was pretty sure that most SQL flavors had a bitwise NOT, so I checked and there does appear to be one in TSQL.

From the documentation, it's the character ~.


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

A 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


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

query (vb)

x = "select x from table"

update (vb)

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

Need Your Help

Erlang-style concurrency in .NET or C++

c# c++ multithreading concurrency erlang

After numerous projects one-way asynchronous message passing looks like the only way to write concurrent distributed network services and keep your sanity.

How much business logic belongs in RIA services layer?

.net silverlight entity-framework architecture wcf-ria-services

I have been experimenting recently with Silverlight, RIA Services, and Entity Framework using .NET 4.0. I'm trying to figure out if that stack makes sense for use in any of my upcoming projects. It