CODEWITHSUNDEEP
sqlsql-server
AzaRoth91
AzaRoth91

Reputation: 283

SQL Integer and '&' sign

Is somebody able to explain what the below query is actually doing?

SELECT (Convert(int, 33558529) & 4096),((Convert(int, 33558529) & 1048576))
FROM dbo.example

Why does the first part return 4096 and the second part returns 0?

Upvotes: 4

Views: 470

Answers (3)

Jamiec
Jamiec

Reputation: 136154

The & sign in T-SQL is the bitwise AND. It is used for bitwise comparison on numbers.

Why does the first part return 4096 and the second part returns 0?

Because the big number (33558529) includes the 4096 bit, but does not contain the 1048576 bit.

I find it easier to understand when you use smaller numbers, and write it out in binary. Suppose the big number you're checking is actually 9, written as binary 9 is

8 4 2 1
=======
1 0 0 1  <-- 9

If we were to perform bitwise AND logic on the above with the number 8 we would get

8 4 2 1
=======
1 0 0 1    <-- 9
1 0 0 0    <-- 8
-------
1 0 0 0  < -- result of ANDing 9 & 8 = 8

If we did the same exercise but with 2

8 4 2 1
=======
1 0 0 1    <-- 9
0 0 1 0    <-- 2
-------
0 0 0 0    <-- result of ANDing 9 & 2 = 0

Upvotes: 3

Ajay2707
Ajay2707

Reputation: 5808

& is the bitwise logical and operator - It performs the operation on 2 integer values.

Please refer : Ampersand (&) operator in a SQL Server WHERE Clause

SELECT 
(Convert(int, 33558529) & 4096),((Convert(int, 33558529) & 1048576))
FROM dbo.example

Its compare the bitwise data and gives the result

Check this link and compare the bit value

 33558529  - 00000010 00000000 00010000 00000001  
 4096      - 00000000 00000000 00010000 00000000 means 1
 1048576   - 00000000 00000000 00000000 00000000

Same you check the logic behind.

Upvotes: 0

ForguesR
ForguesR

Reputation: 3618

& performs a bitwise logical AND operation between two integer values. See the doc.

Here are the integer values converted to binary :

33558529 = 10000000000001000000000001
    4096 =              1000000000000 1 bit match hence 1000000000000 or 4096
 1048576 =      100000000000000000000 0 bit match hence 0

Upvotes: 3

Related Questions