How apply a bitmask to an integer in MySQL?

Question

Here is the problem:

The database Rx30 stores the status of a prescription in a field called 'Rxstatus.' The number they store there is an integer that is never higher than 255. The bits that are set determine whether or not a script is filled.

A representative told us that if 16, 32, or 128 is set, the script is deemed 'unfilled.'

So now what we need to do is take an integer, say '49', and see which bits are set. This needs to be done through MySQL for speed's sake. I understand that 49 is:

32+16+1 = 49

So 16 is set in this field, therefore the script is unfilled.

How (in MySQL) can I take an integer, say 152 for example, and determine which bits of it are set?

Once we determine what's set, if 16, 32, or 128 is set we can deem the script unfilled and produce the correct results.

Answer 1

If you have a test bit mask, e.g. 32+16+1 = 49, and you want to know if any of these are set in Rxstatus, you use:

SELECT Rxstatus & 49 != 0

If you want to know if all of them are set, you use:

SELECT Rxstatus & 49 = 49

To set all the bits from the bitmask, you do:

UPDATE Rx30
SET Rxstatus = Rxstatus | 49
WHERE <condition>

To unset all the bits from the bitmask, you do:

UPDATE Rx30
SET Rxstatus = Rxstatus & ~49
WHERE <condition>

Answer 2

Use the MySQL BIT functions like &

select Rxstatus & 2 = 2 as 2_set, 
       Rxstatus & 4 = 4 as 4_set,
       Rxstatus & 8 = 8 as 8_set,
       Rxstatus & 16 = 16 as 16_set,
       Rxstatus & 32 = 32 as 32_set,
       Rxstatus & 64 = 64 as 64_set,
       Rxstatus & 128 = 128 as 128_set
from your_table

SQLFiddle demo