SQL Server: How to Toggle / Flip / Invert value of Bit column

Introduction: In this article I am going to share some methods to invert / flip / toggle value of a bit field / column in sql server i.e. converting value from 1 to 0 and vice versa. 

In previous articles I have explained How to Generate row number/serial number without ordering by any columns and Row_number(), rank(), dense_rank(), ntile() ranking functions and Using merge in sql server to insert, update and delete in single statement and CTE recursive query to get parent child hierarchy with levels and Function to check column exists in table or not

Implementation:  Let’s create a dummy table for demonstration purpose using the following script.

CREATE  TABLE dbo.tbEmployeeMaster

(

            EmployeeId      INT IDENTITY(1,1) PRIMARY KEY,

            Name               VARCHAR(100),

            Age                  INT,

            IsActive            BIT 

)

--Insert some dummy data into the table

GO

INSERT INTO dbo.tbEmployeeMaster

VALUES

('Aman',28,1),

('Rohan',34,0),

('Varun',25,1),

('Arjun',45,1),

('Raghav',33,0),

('Sameer',27,0);

--Check data in table

SELECT * FROM dbo.tbEmployeeMaster

EmployeeId	Name	Age	IsActive
1	Aman	28	1
2	Rohan	34	0
3	Varun	25	1
4	Arjun	45	1
5	Raghav	33	0
6	Sameer	27	0

Now lets toggle or flip IsActive column value using various ways:

Toggle Bit Value

SELECT

IsActive,

~IsActive AS IsActive,

ABS ( IsActive-1) AS IsActive,

IsActive ^ 1 AS IsActive,

IsActive=1-IsActive,

IsActive=IIF(IsActive=1,0,1),

CASE IsActive WHEN 1 THEN 0 ELSE 1 END AS IsActive

FROM dbo.tbEmployeeMaster

Note : IIF function will work only in sql 2012 or upper version and it returns a value if a condition is TRUE or another value if a condition is FALSE.

IsActive	IsActive	IsActive	IsActive	IsActive	IsActive
1	0	0	0	0	0
0	1	1	1	1	1
1	0	0	0	0	0
1	0	0	0	0	0
0	1	1	1	1	1
0	1	1	1	1	1

As we can see Actual value is in First column and in all other columns value is inverted.

Now let’s see how to use this while updating in table . You can use any of the following Update query to update toggled value in table.

UPDATE dbo.tbEmployeeMaster

SET IsActive = ~IsActive

UPDATE dbo.tbEmployeeMaster

SET IsActive = ABS( IsActive-1)

UPDATE dbo.tbEmployeeMaster

SET IsActive = IsActive ^ 1

UPDATE dbo.tbEmployeeMaster

SET IsActive = 1-IsActive

UPDATE dbo.tbEmployeeMaster

SET IsActive=IIF(IsActive=1,0,1)

UPDATE dbo.tbEmployeeMaster

SET IsActive = CASE WHEN IsActive = 1 THEN 0 ELSE 1 END

Result will be as:

EmployeeId	Name	Age	IsActive
1	Aman	28	0
2	Rohan	34	1
3	Varun	25	0
4	Arjun	45	0
5	Raghav	33	1
6	Sameer	27	1

As we can see all values in IsActive column is inverted.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post