getting the index of set bits from an int column in mysql

Question

Anyone have any SQL-fu (can be MySQL-specific) that will give me the indexes of set bits in an int, without using a procedure? How about with a procedure?

For example, given:

create table example (val int);
insert into example values (1), (2), (3), (4), (256);

I can see the set bits:

select conv(val, 10, 2) from example;
+------------------+
| conv(val, 10, 2) |
+------------------+
| 1                | 
| 10               | 
| 11               | 
| 100              |
| 100000000        | 
+------------------+

I need magic that will give me:

+------------------+
| (something)      |
+------------------+
| 1                | 
| 2                | 
| 1,2              | 
| 3                | 
| 9                | 
+------------------+

.. happy to get 0-based, too.

Answer 1

Here's a starter maybe... At least in T-SQL there is a bit wise operator you can use to combine values with what ever input value. Basically the rule is if input & bitvalue = bitvalue then the bit is turned on.

declare @i int
set @i =40
select @i & 1, @i & 2, @i & 4, @i & 8, @i & 16, @i & 32, 
       @i & 64, @i & 128, @i & 256

Answer 2

The MySQL function MAKE_SET() does this:

SELECT
    MAKE_SET( val, '1', '2', '3', '4', '5', '6', '7', '8', '9' )
FROM
    example

There's probably some other function that will generate the series of number strings too ... anyone?

Answer 3

SELECT TRIM(TRAILING ',' FROM CONCAT(IF(x&1, "1,", ""), 
                                     IF(x&2, "2,", ""),
                                     IF(x&4, "3,", ""), ...))