This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Logical Bitwise Operators

The thirty-fifth part of the SQL Server Programming Fundamentals tutorial investigates the logical bitwise operators. These operators perform operations upon the individual binary digits that are held within numeric or binary data types.

Exclusive OR Operator

The third operator, and the last that requires two operands, is the exclusive OR operator, often abbreviated to XOR. This operator compares the bits of the two operands in a similar manner to AND and OR. Where one bit is zero and the other is one, the resultant bit will be one. Where the two bits in the operands match, the resultant bit will be zero. For example:

  11110000 = 240
  00111100 = 60
  11001100 = 204

To try the above calculation in T-SQL, use the caret symbol (^) as the operator:

PRINT 240 ^ 60

You can use the XOR operator to toggle the values of individual bits in a bit field. To toggle one or more bits, XOR the field's value with the value of the bits that should be reversed. For example, to toggle the "Auto Answer Mode" flag you can use the following calculation:

  00011011 = 27
  00001000 = 8
  00010011 = 19

NOT Operator

The NOT operator is a unary operator, meaning that it requires only a single operand. The operator returns the one's complement of the operand, converting all ones to zeroes and all zeroes to ones. For example:

  00001000 = 8
  11110111 = 247

To calculate the one's complement in T-SQL, the tilde symbol (~) is used as a prefix to the operand. For example, to determine the one's complement of 8, execute the following statements:

SET @Value = 8
PRINT ~@Value

In the example above, the value has been defined as a tiny integer, as this represents a single byte with a value between zero and 255. When using data types that can hold negative values, the results are not as easy to interpret. This is because negative numbers are represented by setting the highest order bit and using two's complement notation. To show this, run the following statement:

PRINT ~8    -- Prints -9

NB: Although the integer representation is different, the operation is correct at the bit level.

Operator Precedence

We can now add the bitwise operators to the table of precedence.

Unary Bitwise Operators
Arithmetic Operators
* / %
Unary Arithmetic Operators
+ -
Arithmetic / Concatenation Operators
[+ arithmetic] [+ concatenation)] -
Bitwise Operators
& ^ |
Comparison Operators
=, >, <, >=, <=, <>, !=, !>, !<
12 September 2009