Search code examples
mysqlsqlbitwise-operators

SQL - Integer Type - Detect bit

With SQL, I would like to detect if a certain bit is on with an integer column type (MySQL)?

How would I go about this?

Basically, I want to detect if a bit is on, if it is on, then ignore.

I am using something like this now:

WHERE (column & 2) != 2
Solution

  • Say you're checking for the 3rd bit then...

    SELECT bits & 8 = 8 FROM table;

    This returns a binary (1 or 0) for whether the bit in question (in this case the third) is on or off for each row in the column 'bits'.

    using 2^x for the xth bit instead of 8.

    for example, to check the 5th bit we would use 2^5 = 32

    SELECT bits & 32 = 32 FROM table;

    However, this is needlessly complicated. Simply use boolean values in several columns and it will make things much easier.