Chapter 1: SQL Building Blocks

Bitwise operators

The bitwise operators are a Transact-SQL extension for use with the datatype integer. These operators convert each integer operand into its binary representation and then evaluate the operands column by column. A value of 1 corresponds to true; a value of 0 corresponds to false.

Table 1-4 and Table 1-5 summarize the results for operands of 0 and 1. If either operand is NULL, the bitwise operator returns NULL:

**Table 1-4: Truth tables for bitwise operations**
& (and)	1	0
1	1	0
0	0	0

\| (or)	1	0
1	1	1
0	1	0

^ (exclusive or)	1	0
1	0	1
0	1	0

~ (not)
1	FALSE
0	0

The following examples use two tinyint arguments: A = 170 (10101010 in binary form) and B = 75 (01001011 in binary form).

**Table 1-5: Examples of bitwise operations**
Operation	Binary form	Result	Explanation
(A & B)	`10101010 01001011 ------------ 00001010`	10	Result column equals 1 if both A and B are 1. Otherwise, result column equals 0.
(A \| B)	`10101010 01001011 ------------ 11101011`	235	Result column equals 1 if either A or B, or both, is 1. Otherwise, result column equals 0.
(A ^ B)	`10101010 01001011 ------------ 11100001`	225	Result column equals 1 if either A or B, but not both, is 1.
(~A)	`10101010 ------------ 01010101`	85	All 1s are changed to 0s and all 0s to 1s.

View this book as PDF