Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have Table A

|-1 |
|0  |
|1  |
|10 |

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

|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.

share|improve this question
up vote 3 down vote accepted

Like this?

share|improve this answer
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))))
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.