Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 242
Billious Avatar asked Sep 09 '09 03:09

Billious


People also ask

How do you flip a bit in SQL?

You can just flip the value of the bit field by prefixing it with ~ before it.

How do I update a bit field in SQL?

1 Answer. Show activity on this post. Bits in SQL Server are always stored as 1 or 0 in a bitmap. The "Edit Table" option in SSMS just translates this to True or False for presentation purposes, this is nothing to do with how it is actually stored.


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

like image 133
Austin Salonen Avatar answered Oct 02 '22 08:10

Austin Salonen