CODEWITHSUNDEEP
sqlsql-servert-sql
Billious
Billious

Reputation: 2583

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.

Upvotes: 84

Views: 44910

Answers (8)

Saksham Gupta
Saksham Gupta

Reputation: 400

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://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-not-transact-sql

Upvotes: 12

Thomas Owens
Thomas Owens

Reputation: 116179

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 ~.

Upvotes: 20

Nasa Rahman
Nasa Rahman

Reputation: 11

query (vb)

x = "select x from table"

update (vb)

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

Upvotes: 1

Leonardo Marques de Souza
Leonardo Marques de Souza

Reputation: 539

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.

Upvotes: 30

gbn
gbn

Reputation: 432311

Why not a simple bitfield = 1 - bitfield?

Upvotes: 56

Mayo
Mayo

Reputation: 10802

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

Upvotes: 14

eKek0
eKek0

Reputation: 23289

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

Upvotes: 1

Austin Salonen
Austin Salonen

Reputation: 50225

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 (^)

Upvotes: 143

Related Questions