Bitwise Operators in SQL Server

Bitwise operators in SQL Server are very much like logical operators but they perform bit manipulations between two expressions of any of the data types of the integer data type category. Bitwise operators convert two integer values to binary bits, perform the AND, OR, or NOT operation on each bit, producing a result. Then converts the result to an integer.

SQL Server supports following bitwise operators:

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

Bitwise AND (&)

The ampersand character (&) is the bitwise AND operator and ampersand symbol with equal symbol is bitwise And Equals To operator.

Take a look at these examples:

In this example, both operands are treated as ‘binary’ 1.

Result: 1

Result: 0

Since 20 and 21 are decimal numbers. SQL Server will convert them to binary (20 = 10100, 21=10101). Bitwise And operator will compare each bit of both operands and produce ‘10100’ which is decimal 20. Bitwise AND returns 1 if both bits are 1.

Result: 20

Bitwise OR (|)

SQL Server uses pipe character (|) for bitwise OR operator. It returns 1 if one or both bits are 1.

See this example:

Since 21 = (Binary) 10101 and 22= (Binary) 10110, result will be:

Result: (Binary)10111 or 23

Bitwise Exclusive OR (^)

T-SQL uses caret character (^) as bitwise XOR (exclusive OR) operator. it returns 1 if both bits are not 0.

Result: 3

You may also like...