CODEWITHSUNDEEP
sqlsql-servert-sqlbitmask
Nick
Nick

Reputation: 5696

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

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.

Upvotes: 61

Views: 58769

Answers (6)

Diomidis Spinellis
Diomidis Spinellis

Reputation: 19345

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.

Upvotes: 7

ScottE
ScottE

Reputation: 21630

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 > 0

Upvotes: 2

Jamiec
Jamiec

Reputation: 136094

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

Upvotes: 104

Ben
Ben

Reputation: 181

SELECT * FROM UserTable WHERE Roles & 6 > 0

Upvotes: 5

vsevik
vsevik

Reputation: 9659

SELECT * FROM table WHERE mask1 & mask2 > 0

Upvotes: 2

Sklivvz
Sklivvz

Reputation: 31133

To find all programmers use:

SELECT * FROM UserTable WHERE Roles & 2 = 2

Upvotes: 0

Related Questions