Search code examples
mysqlbitwise-operators

Excluding values that don't fit the mask - Bitwise operations in mySQL

I need help solving a problem. I have a User with a Bitmask-Value, let's say 10010001.
Column name is value_1. In this column I have this values to compare with: 

|--|----------|  
|id|   value_1|
|--|----------|
| 1| 00000001 |
| 2| 10010001 | 
| 3| 00000010 | 
| 4| 10000001 |
| 5| 10011001 |
|--|----------|

My query is this:

select value_1 from testdb
where b'10010001' & value_1;

My result is ID 1,2,4,5
This looks fine - but how can I exclude id 5 because the lowest 4th bit (counting from the right) is not in source?

Solution

  • Make sure that the combined bits of value_1's value and mask do not result in a number that exceeds the mask as a number.

    where (b'10010001' & value_1) and (cast((b'10010001' | value_1) as unsigned) <= cast(b'10010001' as unsigned))

    Exceeding means that at least one bit is present in value_1's value but not in the mask (source).

    Test it here