Bitwise operators perform bit
manipulations between two expressions of any of the data types of the integer
data type category.
The operands for bitwise
operators can be any of the data types of the integer or binary string data
type categories (except for the image data type), with the exception
that both operands cannot be any of the data types of the binary string data type category.
The table shows the supported
operand data types.
Left operand Right operand
binary int, smallint, or
tinyint
bit int, smallint,
tinyint, or bit
int int,
smallint, tinyint, binary, or varbinary
smallint int,
smallint, tinyint, binary, or varbinary
tinyint
int, smallint, tinyint, binary, or varbinary
varbinary int,
smallint, or tinyint
& (Bitwise AND)
Syntax
expression & expression
Arguments
expression
Is any valid Microsoft® SQL
Server™ expression of any of the data types of the integer data type category. expression
is an integer parameter that is treated and transformed into a binary
number for the 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 AND between the two expressions, taking each
corresponding bit for both expressions. The bits in the result are set to 1 if
and only if both bits (for the current bit being resolved) in the input
expressions have a value of 1; otherwise, the bit in the result is set to 0.
The & bitwise operator
can be used only on expressions 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), 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 values, and puts the table into one row.
SELECT 170 & 75
Here is the result set:
-----------
10
The binary representation of
170 is 0000 0000 1010 1010. The binary representation of 75 is 0000 0000 0100
1011.
Performing the bitwise AND
operation on these two values produces the binary result 0000 0000 0000 1010,
which is decimal 10.
0000 0000 1010 1010
0000 0000 0100 1011
-------------------
0000 0000 0000 1010
declare
@var binary
=1,
@var2 varbinary
=0
select @var & @var2
Msg 402, Level 16, State 1,
Line 6
The data types binary and varbinary
are incompatible in the '&' operator.
declare
@var varbinary
=1,
@var2 varbinary
=0
select @var & @var2
Msg 402, Level 16, State 1,
Line 4
The data types varbinary and
varbinary are incompatible in the '&' operator.
| (Bitwise OR)
Performs a bitwise logical 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. expression
is an integer that is treated and transformed into a binary number for the
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 OR between the two expressions, taking each
corresponding bit for both expressions. The bits in the result are set to 1 if
either or both bits (for the current bit being resolved) in the input
expressions have a value of 1; if neither bit in the input expressions is 1,
the bit in the result is set to 0.
The | bitwise operator
requires two expressions, and it can be used on expressions of only 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), 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.
SELECT 170 | 75
Here is the result set:
-----------
235
The binary representation of
170 is 0000 0000 1010 1010.
The binary representation of
75 is 0000 0000 0100 1011.
Performing the bitwise OR
operation on these two values produces the binary result 0000 0000 1110 1011,
which is decimal 235.
(A | B)
0000 0000 1010 1010
0000 0000 0100 1011
-------------------
0000 0000 1110 1011
declare
@var binary
=1,
@var2 varbinary
=0
select @var | @var2
Msg 402, Level 16, State 1,
Line 4
The data types binary and varbinary
are incompatible in the '|' operator.
declare
@var varbinary
=1,
@var2 varbinary
=0
select @var | @var2
Msg 402, Level 16, State 1,
Line 4
The data types varbinary and
varbinary are incompatible in the '|' operator.
^ (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
SELECT 170 ^ 75
GO
Here is the result set:
-----------
225
The binary representation of
170 is 0000 0000 1010 1010.
The binary representation of 75
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.
(170 ^ 75)
0000 0000 1010 1010
0000 0000 0100 1011
-------------------
0000 0000 1110 0001
No comments:
Post a Comment