Yesterday I wrote a blog post about SQL SERVER – How to Flip Value of Bit Field in SQL Server? . Lots of good suggestions I have received in response of the same. Here are a few different methods to flip value of a bit field suggested by my very good friend Vinod Kumar. All the methods are also very simple and flips the value of the bit field in SQL Server.
DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT @field1 Field1, @field2 Field2
-- Method one
SELECT @field1 = 1^@field1, @field2 = 1^@field2
SELECT @field1 Field1, @field2 Field2
-- Method 2
SELECT @field1 = 1-@field1, @field2 = 1-@field2
SELECT @field1 Field1, @field2 Field2
-- Method 3
SELECT @field1 = (1+@field1)%2, @field2 = (1+@field2)%2
SELECT @field1 Field1, @field2 Field2
-- Method 4
SELECT @field1 = ABS(@field1-1), @field2 = ABS(@field2-1)
SELECT @field1 Field1, @field2 Field2
Though Vinod does not get any award for suggesting alternative methods. If you suggest an interesting solution which does not use CASE statement, you can win a month long Pluralsight subscription.
Reference: Pinal Dave (https://blog.sqlauthority.com)
