SQL What does &

Question

I want to know the function of operator &. For example:

SELECT (8 & 16)

In the last code if I change the second value (16) to another one, like 10, the result changes.

I read about it, but I didn't found an accurate answer.

Thank's for your help.

Answer 1

For SQL Server you can find the answer here https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql?view=sql-server-ver15

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.

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 smallintexpression is converted to an int.

To come back to your Example:

If you compare 2 Integer numbers bitwise this will be calculated:

8 = 0 0 0 0 1 0 0 0 16 = 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 = 0

---

8 = 0 0 0 0 1 0 0 0 10 = 0 0 0 0 1 0 1 0 0 0 0 0 1 0 0 0 = 8

Hope that helps.

Answer 2

In most databases, & is the bitwise AND operator. What it does is translate its integer argument to their bit representation, and apply the AND operator to each individual bit (this is equivalent to multiplying each bit invidually):

For your operation 8 & 16:

• 8 is represented as 1000 in binary
• 16 corresponds to 10000

Performing the bitwise and:

    01000 
AND 10000
    -----   
    00000 (0)

Answer 3

In MS SQL Server it is a bitwise AND operator.

If bits at any location are both 1, the result is 1.

1010 1010 = 170

0100 1011 = 75

0000 1010 = 10

Select 170 & 75

Returns 10