# Bitwise Operators

## Bitwise Operators

Bitwise operators perform bit operations on integer data types; all bitwise operators introduced into SQL by RDBMS vendors are listed in Table 11-6. To understand the results of the bitwise operations one must understand the basics of Boolean algebra.

 Cross-References See Appendix L for more information on Boolean algebra.
Table 11-6: Bitwise Operators

Operator

Description

&

Bitwise AND

|

Bitwise OR

/\

Bitwise exclusive OR

~

Bitwise NOT

The operands for bitwise operators can be of either the integer data type or the binary string data type (except for IMAGE data type) category. Data type compatibility is given in Table 11-7. Bitwise operations are not typical for a high-level language such as SQL, and one might be hard-pressed to come up with a usage example.

Table 11-7: Data Type Compatibility for Bitwise Operands

Left Operand

Right Operand

BINARY

INT, SMALLINT, TINYINT

BIT

INT, SMALLINT, TINYINT, BIT

INT

INT, SMALLINT, TINYINT, BINARY, VARBINARY

SMALLINT

INT, SMALLINT, TINYINT, BINARY, VARBINARY

TINYINT

INT, SMALLINT, TINYINT, BINARY, VARBINARY

VARBINARY

INT, SMALLINT, TINYINT

One of the possible uses could be a complex bit mask made for color — after all RDBMS now supports more than just text and numeric data. Consider a combination of zeroes and ones, for example 0101. When a binary AND is applied to this number and to another binary number, for example 1101, it will produce the following result:

```SELECT 5 & 13 result result
------ 5```

The binary representation of 5 is 0101. In this example of logical AND, only 1 and 1 produce 1, any other combinations — namely 1 and 0, or 0 and 0 — produce 0.

```0101 (decimal 5) 1101 (decimal
13) ---- 0101 (decimal 5)
```

To decode or encode a pixel in an image stored in your database you would use bit mask and shifting. Another possible use of the XOR (exclusive OR) operator would be to encrypt data based on some numeric key.

Only Microsoft SQL Server provides bitwise operators; the IBM DB2 UDB dialect of SQL does not have bit operations support built into the language itself, and Oracle 9i has a BITAND function that works identically to SQL Server's bitwise AND.  Preface  Part I: SQL Basic Concepts and Principles  Part II: Creating and Modifying Database Objects  Part III: Data Manipulation and Transaction Control  Part IV: Retrieving and Transforming Data  Chapter 8: Understanding SELECT Statement  Chapter 9: Multitable Queries  Chapter 10: SQL Functions  Chapter 11: SQL Operators  Arithmetic Operators  Logical Operators  Operator Precedence  Assignment Operator  Comparison Operators  Bitwise Operators  User-defined Operators  Summary  Part V: Implementing Security Using System Catalogs  Part VI: Beyond SQL--Procedural Programming and Database Access Mechanisms  Part VII: Appendix  List of Figures  List of Tables  List of Code Examples  List of Sidebars