Comparing two bitmasks in SQL to see if any of the bits match

Question

Is there a way of comparing two bitmasks in Transact-SQL to see if any of the bits match? I've got a User table with a bitmask for all the roles the user belongs to, and I'd like to select all the users that have any of the roles in the supplied bitmask. So using the data below, a roles bitmask of 6 (designer+programmer) should select Dave, Charlie and Susan, but not Nick.

User Table
----------
ID  Username  Roles
1   Dave      6
2   Charlie   2
3   Susan     4
4   Nick      1

Roles Table
-----------
ID  Role
1   Admin
2   Programmer
4   Designer

Any ideas? Thanks.

Answer 1

Use the Transact-SQL bitwise AND operator "&" and compare the result to zero. Even better, instead of coding the roles as bits of an integer column, use boolean columns, one for each role. Then your query would simply be designer AND programmer. If you expect the roles to change a lot over the lifetime of your application, then use a many-to-many table to map the association between users and their roles. both alternatives are more portable than relying on the existence of the bitwise-AND operator.

Answer 2

To find all programmers use:

SELECT * FROM UserTable WHERE Roles & 2 = 2

Answer 3

After reading what you actually want, you cant do it with you current bit structure. Sometimes you need to adjust the 'layout' a bit. Example (C#):

enum NumberClass
{
  Complex = 1,
  Real = 2 | Complex,
  Rational = 4 | Real ,
  BigInteger = 8 | Rational,
  Integer = 16 | BigInteger,
  NotANumber = 0
}

This is probably not what you are looking for, but it shows you how to use bits different, in this case a number hierarchy.

Alternatively, you could probably just do a union of 'options' you want.

Answer 4

According to your Tables, if you want to return roles (Designer and Programmer) you should only return Dave.

SELECT * FROM UserTable WHERE Roles & 6 = 6

Maybe you failed to phrase the question correctly ?

Answer 5

SELECT * FROM table WHERE mask1 & mask2 > 0

Answer 6

You're looking for a bitwise OR here.

example:

DECLARE @Mask int
SET @Mask = 6

DECLARE @Users TABLE
(
ID int,
Username varchar(50),
Roles int
)

INSERT INTO @Users (ID, Username, Roles) 
SELECT 1, 'Dave', 6
UNION
SELECT 2, 'Charlie', 2
UNION
SELECT 3, 'Susan', 4
UNION
SELECT 4, 'Nick', 1

SELECT * FROM @Users WHERE Roles | @Mask = @Mask