SQL Bitwise Operators

Bitwise Operators

X	Y	X&Y	X|Y	X^Y	~X
0	0	0	0	0	1
0	1	0	1	1	1
1	0	0	1	1	0
1	1	1	1	0	0

See the following topics:

• & (Bitwise AND)
• &= (Bitwise AND Assignment)
• | (Bitwise OR)
• |= (Bitwise OR Assignment)
• ^ (Bitwise Exclusive OR)
• ^= (Bitwise Exclusive OR Assignment)
• ~ (Bitwise NOT)

The operands for bitwise operators can be any one of the data types of the integer or binary string data type categories (except for the image data type), except that both operands cannot be any one of the data types of the binary string data type category. The following table shows the supported operand data types.

Left operand	Right operand
binary	int, smallint, or tinyint
bit	int, smallint, tinyint, or bit
bigint	bigint, int, smallint, tinyint, binary, or varbinary
int	int, smallint, tinyint, binary, or varbinary
smallint	int, smallint, tinyint, binary, or varbinary
tinyint	int, smallint, tinyint, binary, or varbinary
varbinary	int, smallint, or tinyint

Examples:

SELECT BitValue, (BitValue & 1) FROM tblBitLookup WITH (NOLOCK)
SELECT BitValue, (BitValue & 8) FROM tblBitLookup WITH (NOLOCK)
SELECT BitValue, (BitValue & 9) FROM tblBitLookup WITH (NOLOCK)

Remove bit value from table…

UPDATE
	b
SET
	b.BitValue = b.BitValue - 8
FROM 
	dbo.tblBitTable b WITH (NOLOCK) 
WHERE 
	b.BitValue & 8 > 0

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-operators-transact-sql?view=sql-server-ver15