In SQL - how can I count the number of times Bit(0), Bit(1), ... Bit(N) are high for a decimal numbe-CodePudding

I am dealing with a table of decimal values that represent binary numbers. My goal is to count the number of times Bit(0), Bit(1),... Bit(n) are high.

For example, if a table entry is 5 this converts to '101' which can be done using the BIN() function.

What I would like to do is increment a variable 'bit0Count' and 'bit2Count'

I have looked into the BIT_COUNT() function however this would only return 2 for the above example.

Any insight would be greatly appreciated.

CodePudding user response：

To tell if bit N is set, use 1 << N to create a mask for that bit and then use bitwise AND to test it. So (column & (1 << N)) != 0 will be 1 if bit N is set, 0 if it's not set.

To total these across rows, use the SUM() aggregation function.

If you need to do this frequently, you could define a stored function:

CREATE FUNCTION bit_set(UNSIGNED INT val, TINYINT which) DETERMINISTIC
    RETURN  (val & (1 << which)) != 0;

CodePudding user response：

SELECT SUM(n & (1<<2) > 0) AS bit2Count FROM ...

The & operator is a bitwise AND.

1<<2 is a number with only 1 bit set, left-shifted by two places, so it is binary 100. Using bitwise AND against you column n is either binary 100 or binary 000.

Testing that with > 0 returns either 1 or 0, since in MySQL, boolean results are literally the integers 1 for true and 0 for false (note this is not standard in other implementations of SQL).

Then you can SUM() these 1's and 0's to get a count of the occurrences where the bit was set.