SQL SERVER: HOW TO TOGGLE / FLIP / INVERT VALUE OF BIT COLUMN

SQL SERVER: HOW TO TOGGLE / FLIP / INVERT VALUE OF BIT COLUMN

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

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.

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:

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