Using 0x800 in SQL WHERE Clause

I am trying to decipher some SQL statements using, SQL Profiler, that are run from a proprietary application.

One of the statements is:

SELECT ID, Groups, Name, Gallery FROM DashboardReports WHERE (Groups & 0x800) <> 0 AND Root = 1 ORDER BY Name 

Can anyone explain how the WHERE clause works? I've never seen a WHERE clause like that before and the "Groups" column contains integer values such as 2048,2176,150 AND 414.

Thanks Danny

1 Answer

This is a bitwise operation -

0x800 in hexadecimal is 2048 so it would be the same as writing (Groups & 2048) <> 0

This where clause is telling the query to filter out any records that have the 12th bit in the Groups column turned off.

2048 means that the 12th bit is turned on (it makes more sense when you look at the number as a binary value).

0000 1000 0000 0000 = 2048 

it's a convenient way to store several on/off values in a single column. 150 is a combination of bits (128 and 32) both these would evaluate as true:

(Groups & 128) <> 0 (Groups & 32) <> 0 

Here's a quick list of the bits and their values for you:

1 1 2 2 3 4 4 8 5 16 6 32 7 64 8 128 9 256 10 512 11 1024 12 2048 

This article may help -