Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Like this?

 INSERT INTO b SELECT FLOOR(LOG2(Col1)) + 1 FROM a;
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
add comment

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
add comment

Your Answer

 
discard

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.