How to simulate the MySQL bit_count function in Sybase SQL Anywhere?

Question

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?

Answer 1

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;
}