SQL SERVER – How to Flip Value of Bit Field in SQL Server? – Part 2

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

SQL SERVER - How to Flip Value of Bit Field in SQL Server? - Part 2 flipbitfield1

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)

Previous Post
SQL SERVER – How to Flip Value of Bit Field in SQL Server?
Next Post
SQL Authority News – Embarcadero DB PowerStudio Promotion & Savings

Related Posts

No results found.

4 Comments. Leave new

  • sandeepmittal11
    October 26, 2014 11:15 am

    DECLARE @field1 BIT
    DECLARE @field2 BIT
    SET @field1 = 0
    SET @field2 = 1
    SELECT ~@field1 Field1, ~@field2 Field2

    Reply
  • Please suggest how we can apply the same logic for integer values

    Reply
  • DECLARE @Field1 BIT = 1, @Field2 BIT = 0
    SELECT @Field1 Field1, @Field2 Field2
    SELECT ~@Field1 Field1, ~@Field2 Field2
    –uses the tilde for bitwise NOT.

    Reply
  • DECLARE @field1 BIT
    DECLARE @field2 BIT
    SET @field1 = 0
    SET @field2 = 1
    SELECT @field1 Field1, @field2 Field2

    SELECT @field1+1 Field1, @field2-1 Field2

    Reply

Leave a Reply