What does the & symbol mean in this TSQL query?

Question

I have been working on some reports and recently found a small guide for the system I have been querying against. Part of the syntax it offers up is like the following:

... "WHERE [FIELD] & [VALUE] = [VALUE]"

i.e.: ... "WHERE flags & 16 = 16"

I am just curious as to what this syntax is meaning... I get something like WHERE flags = 16, but not the '&16 = 16' part. Any clarification?

The article referenced: http://rightfaxapi.com/advanced-sql-for-manipulating-faxes/

Thank you, Wes

That's a [bitwise AND operator](http://technet.microsoft.com/en-us/library/ms176122.aspx) Detailed info: http://en.wikipedia.org/wiki/Bitwise_operation — Michael Berkowski, Feb 03 '14 at 20:02
absent any context, the title "Advanced SQL for Manipulating Faxes" is kind of terrifying. — Michael Berkowski, Feb 03 '14 at 20:04
haha. sorry Michael. It's for a system called RightFax. It stored fax details in a SQL Server Database. — Wes, Feb 03 '14 at 21:12

score 5 · Accepted Answer · answered Feb 03 '14 at 20:05

The & is doing a bit-wise "and". So, it is "1" only when both bits are 1. The logic overall is checking that all the bits in value are set in field.

As an example, consider that value is:

0000 0101

Then if field is

1111 1111

The & is:

0000 0101

(The 1s are only where both are 1.)

And this is the same as value. So, this passes the condition.

Now if field is:

0001 1110

Then the & is:

0000 0100

And this differs from value, so it does not pass the condition.

Thanks Gordon. Really appreciate the explanations. – Wes Feb 03 '14 at 21:15 — Wes, Feb 03 '14 at 21:15

score 2 · Answer 2 · answered Feb 03 '14 at 20:34

& is bitwise AND. In your example, your mask is 16(0x0010, binary 0000 0000 0001 0000 ). The reslt of the AND operation will be either 0 (all bits in the mask are not set) or the mask value (all bit in the mask ARE set). So your where A&16 = 16 expression is testing to see if bit 5 of the integer value, counting from the right, is set.

Examples:

48 & 16 = 16 is TRUE:

    48 (binary: 0000 0000 0011 0000)
AND 16 (binary: 0000 0000 0001 0000)
    -- -----------------------------
    16 (binary: 0000 0000 0001 0000)

33 & 16 = 16 is FALSE:

    33 (binary: 0000 0000 0010 0001)
AND 16 (binary: 0000 0000 0001 0000)
    -- -----------------------------
     0 (binary: 0000 0000 0000 0000)

Easy!

What does the & symbol mean in this TSQL query?

2 Answers2