How to flip bit fields in T-SQL?

Question

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.

Solution 1

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

Solution 2

Why not a simple bitfield = 1 - bitfield?

Solution 3

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.

Solution 4

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

Solution 5

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

View more solutions

Comments

[Shannon Severance]

This will set MyBitField to 1 when it starts as NULL. Not exactly flipping the bit.

[Mayo]

I figured it was self evident... but I'll add info about handling nulls just in case.

[LJM]

I would tend to agree with some of the other guys and go for the ~ or the "^ 1 ". Especially if this is a one time thing (just seems cleaner). However, if this a something that will stick around and maintainability is in question, this solution is certainly the most straight forward.

[Billious]

Great! Thanks for that - it works like a charm. I obviously need to learn a bit more about how to use binary operators

[Billious]

In my case this was a one-off query, so maintainability wasn't an issue, but yes, this would have worked equally well. I actually had written a similar query but had trouble getting the CASE statement to work properly. I now realise it was a simple syntax error. D'Oh!

[Billious]

An ingenious and elegant approach, especially for a one-off query, where readability isn't important.

[Even Mien]

Another article: blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/…

[Cᴏʀʏ]

Keep in mind that bitFieldY ^ 1 will return an int, so if you need it to be a bit again, CAST or CONVERT it back to bit.

[John W.]

SELECT ~MyBitField from MyTable In other words Update MyTable set MyBitField = ~MyBitField

[KMX]

@Billious, and GBN you both are right, but I guess Austin's reply is good enough to understand the objective in its clear meaning. Flipping a bit with 1-x will confuse the new commers and hence is not a proper way of achieving it. I also agree that there will be more than one approaches of doing something but if there is a recommended way to do it we should go with it, which is presumably faster than a non-recommended approach.

[Norbert Norbertson]

I like this one too. I find sometimes that if I come across a symbol I don't understand in code that if can be harder to work out the coder's intentions. But this is obvious.

[Uwe Keim]

Maybe for a nullable bitfield, an approach would be bitfield = 1 - ISNULL(bitfield,0). See ISNULL in the documentation.

[ZygD]

@UweKeim Personally, I don't use NULLable bit. Never really needed 3 states for bit

[Uwe Keim]

Thanks, @gbn. I sometimes do when having a legacy table where I add a new bitfield column later on. (Probably this is solvable much more elegant with more knowledge).

[Carlos]

Same idea but turns NULLS to true: UPDATE TABLE_NAME SET BIT_FIELD= IIF(BIT_FIELD IS NULL,1,~ BIT_FIELD)

[Erick de Vathaire]

this is not going to work even in vb and the question is for t-sql