MySQL's bit_count function is quite useful for some cases:
http://dev.mysql.com/doc/refman/5.5/en/bit-functions.html#function_bit-count
Now I would like to use that function in other databases, that do not support it. What's the simplest way to do it (without creating a stored function, as I do not have access to the client database on a DDL level).
One pretty verbose option is this (for TINYINT
data types):
SELECT (my_field & 1) +
(my_field & 2) >> 1 +
(my_field & 4) >> 2 +
(my_field & 8) >> 3 +
(my_field & 16) >> 4 +
...
(my_field & 128) >> 7
FROM my_table
In the case of Sybase SQL Anywhere, the >>
operator doesn't seem to be available, so a division by 2, 4, 8, 16
works as well.
Any other, less verbose options?
I've found this algorithm that's a little less verbose in Java's Integer
and Long
classes. I'm completely oblivious to why it's supposed to work this way, though:
public static int bitCount(int i) {
// HD, Figure 5-2
i = i - ((i >>> 1) & 0x55555555);
i = (i & 0x33333333) + ((i >>> 2) & 0x33333333);
i = (i + (i >>> 4)) & 0x0f0f0f0f;
i = i + (i >>> 8);
i = i + (i >>> 16);
return i & 0x3f;
}