Jimmymcnulty Jimmymcnulty - 1 year ago 60
SQL Question

Ampersand (&) operator in a SQL Server WHERE Clause

Sorry for the very basic question. What does the

operator do in this SQL

WHERE (sc.Attributes & 1) = 0

is an alias for a table which contains a column

I'm trying to understand some SQL in a report and that line is making it return 0 entries. If I comment it out it works. I have limited SQL knowledge and I'm not sure what the
& 1
is doing.


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

WHERE (sc.Attributes & 1) = 0 

The above code checks to see if sc.Attributes is an even number. Which is the same as saying that the first bit is not set.

Because of the name of the column though: "Attributes", then the "1" value is probably just some flag that has some external meaning.

It is common to use 1 binary digit for each flag stored in a number for attributes. So to test for the first bit you use sc.Attributes&1, to test for the second you use sc.Attributes&2, to test for the third you use sc.Attributes&4, to test for the fourth you use sc.Attributes&8, ...

The = 0 part is testing to see if the first bit is NOT set.

Some binary examples: (== to show the result of the operation)

//Check if the first bit is set, same as sc.Attributes&1
11111111 & 00000001 == 1
11111110 & 00000001 == 0
00000001 & 00000001 == 1

//Check if the third bit is set, same as sc.Attributes&4
11111111 & 00000100 == 1
11111011 & 00000100 == 0
00000100 & 00000100 == 1