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.

martin clayton · Accepted Answer · 2009-11-07 01:07:05Z

3

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?

answered Nov 7, 2009 at 1:07

martin clayton

78.6k33 gold badges220 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maas Over a year ago

You know, I was looking at MySQL sets and wishing the implementation wasn't confined to table structures. Thanks for this, exactly what I needed.

Andomar · Accepted Answer · 2009-11-07 01:02:51Z

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

edited Nov 7, 2009 at 1:02

Andomar

239k55 gold badges387 silver badges412 bronze badges

answered Nov 7, 2009 at 0:44

Jim L

2,32717 silver badges20 bronze badges

1 Comment

Andomar Over a year ago

+1 So does MySQL dev.mysql.com/doc/refman/5.0/en/…

Keith Randall · Accepted Answer · 2009-11-07 03:21:13Z

0

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

answered Nov 7, 2009 at 3:21

Keith Randall

23.3k3 gold badges38 silver badges54 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related