Comparing Two Bitmasks in SQL to See If Any of the Bits Match

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

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

Matching bitmasks using bitstrings (instead of ints) in SQL

One way would be to just use a bit string on the right side of the expression, too:

WITH test (id, username, roles) AS (
   VALUES
     (1,'Dave',B'001')
    ,(2,'Charlie',B'011')
    ,(3,'Susan',B'101')
    ,(4,'Nick',B'110')
   )
SELECT *, (roles & B'001') AS intersection
FROM   test
WHERE  (roles & B'001') <> B'000';

Or you can cast an integer 0 to bit(3)

...
WHERE  (roles & B'001') <> 0::bit(3);

You may be interested in this related answer that demonstrates a number of ways to convert between boolean, bit string and integer:

Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?

Be aware that storing the data as integer can save some space. integer needs 4 bytes for up to 32 bit of information, while - I quote the manual at said location:

A bit string value requires 1 byte for each group of 8 bits, plus 5 or
8 bytes overhead depending on the length of the string [...]

comparing bit array to bit mask in tsql

I did some more research and the best solution is to compare masks with bitwise AND operator like this

WHERE mask1 & mask2 <> 0

This is easy, simple and cohesive.

How do I compare (AND) two strings which represent a binary mask in mysql?

You can use conv, eg.

select conv('1100', 2, 10) & conv('0110', 2, 10);

Re comment, it seems to work for me:

mysql> select conv('1001', 2, 10) & conv('0110', 2, 10) = 0;
+-----------------------------------------------+
| conv('1001', 2, 10) & conv('0110', 2, 10) = 0 |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('1001', 2, 10) & conv('0111', 2, 10) = 0;
+-----------------------------------------------+
| conv('1001', 2, 10) & conv('0111', 2, 10) = 0 |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)

Bit logic in SQL

SQL Server also supports bitwise Operators:

http://msdn.microsoft.com/en-us/library/ms176122.aspx

Example:

DECLARE @isValid bit 
SET @isValid = 3 & 1

& operator usage in transact sql

& is bitwise AND (only 1 & 1 => 1):

LiveDemo

CREATE TABLE #tFlags(Flags INT);

INSERT INTO #tFlags VALUES (524675), (525698);

select *
       ,[bitwise AND] = CONCAT(Flags, '& 1 = ')
       ,[result]      = Flags & 1  
from #tFlags;

How it works:

000010000000000110000011   524675
000000000000000000000001   1       &
------------------------
000000000000000000000001   1

and:

000010000000010110000010   525698
000000000000000000000001   1       &
------------------------
000000000000000000000000   0   

The simple answer is:

• odd number & 1 = 1
• even number & 1 = 0

EDIT:

Number & 255: You can get rid of data except byte one.

00000001 00101100    300
00000000 11111111    255   &
-----------------
00000000 00101100    44

The point is you can treat binary number and bitwise operation as masking and use it to set/reset/xor value based on specific position.

Can LINQ (to SQL) do bitwise queries?

I think this will work, but I haven't tested it.Substitute the name of your DataContext object. YMMV.

from u in DataContext.Users
where UserRolesBitmask | 22 == 22
select u

Tsql & operator

This is bitwise AND. In fact, as written, what you return in the SELECT alternates between 0 and 64 and no other numbers.

This is verbatim from https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql :

The & bitwise operator performs a bitwise logical AND between the two
values, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current
bit being resolved) in the input expressions have a value of 1;
otherwise, the bit in the result is set to 0.

Lets see what this does:

DECLARE @myint int = 16
SELECT @myint & 64 [myint 0]  --0

/*
--This is the bitwise AND representation for 16 &64:

0000 0000 0100 0000  --&64
0000 0000 0001 0000  --@MyVar = 16
-------------------  
0000 0000 0000 0000  -- = 0 = 'Yes'
*/

SET @myint = 64
SELECT @myint & 64 [myint 64]  --64

/*
--This is the bitwise AND representation for 64 &64:   

0000 0000 0100 0000  --&64
0000 0000 0100 0000  --@MyVar = 64
-------------------  
0000 0000 0100 0000  -- = 64 = 'No'    
*/

This applies for other numbers as well, try 127 and 128:

/*
0000 0000 0100 0000  --&64
0000 0000 0111 1111  --@MyVar = 127
-------------------  
0000 0000 0100 0000  --64 = 'No' 

0000 0000 0100 0000  --&64
0000 0000 1000 0001  --@MyVar = 128
-------------------  
0000 0000 0000 0000  --0 = 'Yes'
*/

127 &64 = 64.

128 &64 = 0.

---

Related Topics