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

Nick_Crowther_14072 0
Viewed 52220

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.

bitmask tsql sql-server sql
6 Answers

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 friendly. 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.

Diomidis_Spinellis_205200

To find all programmers use:

SELECT * FROM UserTable WHERE Roles & 2 = 2
Sklivvz_70280
Related