sql server - 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

The answer to your question is to use the Bitwise & like this:

SELECT * FROM UserTable WHERE Roles & 6 != 0

The 6 can be exchanged for any combination of your bitfield where you want to check that any user has one or more of those bits. When trying to validate this I usually find it helpful to write this out longhand in binary. Your user table looks like this:

        1   2   4
------------------
Dave    0   1   1
Charlie 0   1   0
Susan   0   0   1   
Nick    1   0   0

Your test (6) is this

        1   2   4
------------------
Test    0   1   1

If we go through each person doing the bitwaise And against the test we get these:

        1   2   4
------------------
Dave    0   1   1   
Test    0   1   1
Result  0   1   1 (6)

Charlie 0   1   0
Test    0   1   1
Result  0   1   0 (2)

Susan   0   0   1
Test    0   1   1
Result  0   0   1 (4)

Nick    1   0   0
Test    0   1   1
Result  0   0   0 (0) 

The above should demonstrate that any records where the result is not zero has one or more of the requested flags.

Edit: Here's the test case should you want to check this

with test (id, username, roles)
AS
(
    SELECT 1,'Dave',6
    UNION SELECT 2,'Charlie',2
    UNION SELECT 3,'Susan',4
    UNION SELECT 4,'Nick',1
)
select * from test where (roles & 6) != 0  // returns dave, charlie & susan

or

select * from test where (roles & 2) != 0 // returns Dave & Charlie

or

select * from test where (roles & 7) != 0 // returns dave, charlie, susan & nick