Tsql & operator

Question

I just came across this command that I have never is seen being used before. What is the & operator doing in this line of code? It seams like ( @MyVar & 64 ) another way of writing @MyVar = 64

         DECLARE    @MyVar INT
         SET @MyVar = 16 -- Prints yes
         SET @MyVar = 64 -- Prints no


         IF ( ( @MyVar & 64 ) = 0 )
         BEGIN
                 SELECT     'yes'
         END
         ELSE
         BEGIN
                 SELECT     'no'
         END

Its a *Bitwise AND*, it compares the equality of each bit in the 2 integers which is most often used when working with a Bitmask to compose multiple values within a single integer, E.g. https://stackoverflow.com/questions/143712/comparing-two-bitmasks-in-sql-to-see-if-any-of-the-bits-match — Alex K., Nov 22 '17 at 18:37
Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Nov 22 '17 at 19:05

score 3 · Accepted Answer · answered Nov 22 '17 at 21:01

This is bitwise AND. In fact, as written, what you return in the SELECT alternates between 0 and 64 and no other numbers.

This is verbatim from https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql :

The & bitwise operator performs a bitwise logical AND between the two values, 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.

Lets see what this does:

DECLARE @myint int = 16
SELECT @myint & 64 [myint 0]  --0

/*
--This is the bitwise AND representation for 16 &64:

0000 0000 0100 0000  --&64
0000 0000 0001 0000  --@MyVar = 16
-------------------  
0000 0000 0000 0000  -- = 0 = 'Yes'
*/

SET @myint = 64
SELECT @myint & 64 [myint 64]  --64

/*
--This is the bitwise AND representation for 64 &64:   

0000 0000 0100 0000  --&64
0000 0000 0100 0000  --@MyVar = 64
-------------------  
0000 0000 0100 0000  -- = 64 = 'No'    
*/

This applies for other numbers as well, try 127 and 128:

/*
0000 0000 0100 0000  --&64
0000 0000 0111 1111  --@MyVar = 127
-------------------  
0000 0000 0100 0000  --64 = 'No' 


0000 0000 0100 0000  --&64
0000 0000 1000 0001  --@MyVar = 128
-------------------  
0000 0000 0000 0000  --0 = 'Yes'
*/

127 &64 = 64.

128 &64 = 0.

Tsql & operator

1 Answers1