Bitwise Operation in SQL Server

There are three basic operators for bitwise operations i.e.  &(Bitwise AND), |(Bitwise OR), and ^(Bitwise Exclusive OR).

These operators perform logical operations against integer type data.

Bit & operation - bits in the result are set to 1 if and only if both bits in the input expressions have a value of 1 bits in the result are set to 0 for any other combination

Bit | operation - bits in the result are set to 1 if either of the corresponding bits is a 1 bits in the result are set to 0 when both values are 0 in the corresponding bit

Bit ^ operation - bits in the result are set to 1 if 1 of the two bits (but not both) has a value of 1 bits in the result are set to 0 Any other combination (either all 0s or all 1s)

DECLARE @Status INT -- DECLARE VARIABLE OF INT
SET @Status=0 -- SET VALUE TO O
set @Status = @Status |1 -- MAKE FIRST BIT ON [FOR THAT USE '|' (BITWISE OR)]
select @Status AS 'AFTER FIRST BIT ON' -- SELECT THE VARIABLE
set @Status = @Status |2 -- MAKE SECOND BIT ON [FOR THAT USE '|' (BITWISE OR)]
select @Status AS 'AFTER SECOND BIT ON' -- SELECT THE VARIABLE

NOW YOU HAVE TWO BITS ARE ON i.e. 1 and 2

SELECT @Status AS 'SECOND BIT ON OR NOT'
WHERE (@Status & 2) = 2 -- CHECK THAT SECOND BIT IS ON OR NOT
SET @Status = ((@Status) & ~2) -- RESET THE SECOND BIT
SELECT @Status AS 'AFTER RESET SECOND BIT'-- SELECT THE VARIABLE
SELECT @Status AS 'SECOND BIT ON OR NOT'
WHERE (@Status & 2) = 2 -- CHECK THAT SECOND BIT IS ON OR NOT
SET @Status = @Status |4 -- MAKE THIRD BIT ON [FOR THAT USE '|' (BITWISE OR)]
SELECT @Status AS 'AFTER THIRD BIT ON' -- SELECT THE VARIABLE

You can reset second bit and set third bit in a single sql statement  
  SET @Status = ((@Status) & ~2)|4
Ebook Download
View all
Learn
View all