Funny, I've had the same requirement, at almost the same time.
I first used Tom's base conversion routines (to_bin)
http://asktom.oracle.com/tkyte/hexdec/index.html Example of calculating the 1's in the binary representation of 789:
SQL> Select 789 N, to_bin(789) bin,
2 nvl(
3 length(
4 replace(
5 to_bin(789)
6 ,'0','')
7 )
8 ,0) NUMBER_OF_1S from dual;
N BIN NUMBER_OF_1S
---------- --------------- ------------
789 1100010101 5
But then, to avoid the overhead involved in calling plsql from sql, I've come up with another solution:
SQL> Select 789 N, to_bin(789) bin ,
2 nvl(
3 length(
4 replace(
5 replace(
6 replace(
7 translate(
8 to_char(789,'FMXXXXXXXX')
9 ,'24839AC657DEB0','1112222223333')
10 ,'2','11')
11 ,'3','111')
12 ,'F','1111')
13 )
14 ,0) NUMBER_OF_1S from dual;
N BIN NUMBER_OF_1S
---------- --------------- ------------
789 1100010101 5
The number to be processed is first converted to hexadecimal and then, every digit is replaced by the number of 1's in its binary representation. Finally, the 1's are counted with the length function.
I've tested the second algorithm against the first for all 2 bytes numbers:
SQL> with test
2 as
3 (select level-1 n
4 from dual
5 connect by level <= power( 2,16)
6 )
7 select n from test
8 where nvl(length(replace(replace(replace( translate(to_char(n,'FMXXXXXXXX'),
9 '24839AC657DEB0','1112222223333'),'2','11'),'3','111'),'F','1111')),0)
10 <> nvl(length(replace(to_bin(n),'0','')),0) ;
no rows selected
And, with oracle9iR2, I've checked the execution times when they are used inside SQL.
with test1
as
(select level-1 n
from dual
connect by level <= 1000000
)
select sum(nvl(length(replace(to_bin(n),'0','')),0)) n_total_1s
from test1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 125.67 125.75 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 125.67 125.75 0 3 0 1
********************************************************************************
with test2
as
(select level-1 n
from dual
connect by level <= 1000000
)
select sum( nvl(length(replace(replace(replace( translate(to_char(n,'FMXXXXXXXX')
,'24839AC657DEB0','1112222223333'),'2','11'),'3','111'),'F','1111')),0) ) n_total_1s
from test2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.93 5.92 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.93 5.92 0 3 0 1
Hope this can help you!
(Don't forget to adjust the format in the to_char function if you are going to analyze numbers bigger than 4 bytes.)