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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
4 Comments. Leave new
DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT ~@field1 Field1, ~@field2 Field2
Please suggest how we can apply the same logic for integer values
DECLARE @Field1 BIT = 1, @Field2 BIT = 0
SELECT @Field1 Field1, @Field2 Field2
SELECT ~@Field1 Field1, ~@Field2 Field2
–uses the tilde for bitwise NOT.
DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT @field1 Field1, @field2 Field2
SELECT @field1+1 Field1, @field2-1 Field2