# Calculate MSB on MySQL

Suppose I have Table A

``````|-10|
|-1 |
|0  |
|1  |
|10 |
|100|
``````

and take the most significant bit position of each value and import it into Table B, like so:

``````|-4|
|-1|
|0 |
|1 |
|4 |
|7 |
``````

How do we do it efficiently? Is there a user-defined function that mimics the behavior of `__builtin_clz` as with GCC?

UPDATE: Added example for zero and negative values.

-

Like this?

`````` INSERT INTO b SELECT FLOOR(LOG2(Col1)) + 1 FROM a;
``````
-
Awesome! However, it returns NULL for zero and negative values... how do you cover them? I updated the question to include more examples. – kenn Aug 23 '12 at 2:21
I'm accepting this as the answer, because the basic idea is well put. Thank you! – kenn Aug 23 '12 at 2:56
I'd argue the NULLs are valid, because binary doesn't handle negative numbers, or even zero. But yes, you can solve that with IF, to set them to whatever you need. :) – Rob Farley Aug 23 '12 at 3:20

Thanks to Rob Farley, this worked.

``````SELECT IF(i=0, 0, IF(i>0, FLOOR(LOG2(i))+1, -FLOOR(LOG2(ABS(i)))-1))
``````

However, there's a caveat - if the column is `bigint` (it is in my case), large numbers in Table A can't be converted correctly.

UPDATE: found a solution that doesn't have precision problems!

``````SELECT IF(i=0, 0, IF(i>0, length(conv(i, 10, 2)), -length(conv(-i, 10, 2))))
``````
-