SQL bitwise operators

Bitwise operators perform bitwise operations on bit patterns or binary numerals.

&

The & (bitwise AND) operator compares each bit of the left operand to the corresponding bit of the right operand. If both bits are 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.

SELECT 5 & 3

|

The | (bitwise OR or inclusive OR) operator compares each bit of the left operand to the corresponding bit of the right operand. If either bit is 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.

SELECT 5 | 3

^

The ^ (bitwise XOR or exclusive OR) operator compares each bit of the left operand to the corresponding bit of the right operand. If the bit in one of the operands is 0 and the bit in the other operand is 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.

SELECT 5 ^ 3

>>

The >> (bitwise shift right) operator shifts the bits in the left operand to the right by the number of positions specified in the right operand. For unsigned numbers, bit positions vacated by the shift operation are filled with 0. For signed numbers, the sign bit is used to fill the vacated bit positions. If the number is positive, the bit position is filled with 0. If the number is negative, the bit position is filled with 1.

SELECT 5 >> 3

<<

The << (bitwise shift left) operator shifts the bits in the left operand to the left by the number of positions specified in the right operand. Bit positions vacated by the shift operation are filled with 0. Bits that shift off the end are discarded, including the sign bit.

SELECT 5 << 3