Many Ways to Flip a Bit in T-SQL

I found myself organizing my code snippets the other day. (Go ahead and poke fun at the Type A coder. I deserve it.) I have a folder dedicated to “SQL – Common Routines.” There within, I came across “Flip Bit.sql.” Over the years, I’ve come across quite a few ways to flip a bit in a boolean column so I was a little curious how this snippet would read.

Here’s what I know. To be honest, I’m not certain which of the four routines perform the best, but I’d put my money on # 3.

# 1. There is the brute force method. I like this method primarily because it is somewhat self-documenting and there is absolutely no question what the coder intends to do here.

use northwind
update products set discontinued = case when discontinued = 1 then 0 else 1 end

# 2. The second option is to use the bitwise XOR operator with either a ‘0’ or a ‘1’. What does this do? The XOR operator returns ‘0’ if the two arguments match and ‘1’ if they don’t.

use northwind
update product set discontinued = discontinued ^ 1

# 3. There is also the bitwise NOT operator. The NOT operator converts each bit in a numeric expression of integer data type into its complement. In simplest terms, ‘0’ become ‘1’ and vica versa.

use northwind
update products set discontinued = ~discontinued

# 4. Finally, one may use MOD (which I surprisingly found in my snippet collection.)

use northwind
update products set discontinued = ((discontinued + 1) % 2)

I’m not done organizing yet. There may be more to come…

Comments

  1. I just found your page after doing this:

    use northwind
    update product set discontinued = abs(isnull(discontinued, 0) – 1)

    Good stuff 🙂 thanks!

  2. Des blogs pornos à 0 euros, il en existe énormément mais des comme celui ci tu n’en verras pas. Chez nous, on propose tous les styles de videos sexy qu’on peut trouver sur Internet. J’espère te voir arriver dès aujourd’hui sur notre blog XXL ! Attention à

  3. I used to be recommended the blog via my best relation. I am now not sure whether the publish is certainly penned via her mainly because no one other than them recognize these types of exact approximately my best problem. You are amazing! Thank you!

closed