^ (Bitwise Exclusive OR)

Performs a bitwise exclusive OR operation between two given integer values as translated to binary expressions within Transact-SQL statements.

Syntax

expression ^ expression

Arguments

expression

Is any valid Microsoft® SQL Server™ expression of any of the data types of the integer data type category, or of the binary or varbinary data type. expression is an integer that is treated and transformed into a binary number for the bitwise operation.

Note  Only one expression can be of either binary or varbinary data type in a bitwise operation.

Result Types

Returns an int if the input values are int, a smallint if the input values are smallint, or a tinyint if the input values are tinyint.

Remarks

The bitwise ^ operator performs a bitwise logical ^ between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either (but not both) bits (for the current bit being resolved) in the input expressions have a value of 1; if both bits are either a value of 0 or 1, the bit in the result is cleared to a value of 0.

The ^ bitwise operator can be used only on columns of the integer data type category.

If the left and right expressions have different integer data types (for example, the left expression is smallint and the right expression is int), then the argument of the smaller data type is converted to the larger data type. In this example, the smallint expression is converted to an int.

Examples

This example creates a table with int data types to show the original values, and puts the table into one row.

USE master
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'bitwise')
   DROP TABLE bitwise
GO
CREATE TABLE bitwise
( 
 a_int_value int NOT NULL,
b_int_value int NOT NULL
)
GO
INSERT bitwise VALUES (170, 75)
GO

This query performs the bitwise exclusive OR on the a_int_value and b_int_value columns.

USE MASTER
GO
SELECT a_int_value ^ b_int_value
FROM bitwise
GO

Here is the result set:

----------- 
225         

(1 row(s) affected)

The binary representation of 170 (a_int_value or A, below) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B, below) is 0000 0000 0100 1011. Performing the bitwise exclusive OR operation on these two values produces the binary result 0000 0000 1110 0001, which is decimal 225.

(A ^ B)   
         0000 0000 1010 1010
         0000 0000 0100 1011
         -------------------
         0000 0000 1110 0001

See Also

Expressions

Operators (Bitwise Operators)