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 XOR 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 XOR 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 NOT 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:

DECLARE @Value TINYINT 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 |

=, >, <, >=, <=, <>, !=, !>, !< |