Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

^ (Bitwise Exclusive OR) (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

Performs a bitwise exclusive OR operation between two integer values.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

expression ^ expression


Is any valid expression of any one of the data types of the integer data type category, or the bit, or the binary or varbinary data types. expression is treated as a binary number for the bitwise operation.


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

int if the input values are int.

smallint if the input values are smallint.

tinyint if the input values are tinyint.

The ^ bitwise operator performs a bitwise logical exclusive OR 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 0 or both bits are 1, the bit in the result is cleared to a value of 0.

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

The following example creates a table using the int data type to store the original values and inserts two values into one row.

USE tempdb;
    WHERE TABLE_NAME = 'bitwise')
DROP TABLE bitwise;
a_int_value int NOT NULL,
b_int_value int NOT NULL
INSERT bitwise VALUES (170, 75);

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

USE tempdb;
SELECT a_int_value ^ b_int_value
FROM bitwise;

Here is the result set:


(1 row(s) affected)

The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) 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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft