count the number of bits set to 1 in a bit string field

Hi,

I don't quite know what you mean by a "bit string field" but for any
numeric, better integer row can use the following construct:

Say the bits you where testing where bit 3 ('00000100' bit),
and bit 6 ('00100000' bit) of say some_integer row....

then this would do it:

select
sum(case when (some_int & x'0004'::int)=0 then 0 else 1 end)
as count_of3thbit,
sum(case when (some_int & x'0020'::int)=0 then 0 else 1 end)
as count_of6thbit
from mytable ;

constant x'0004'::int = 4
constant x'0020'::int = 32

by best regards,

Stefan

Am Sonntag, 15. Juli 2007 21:35 schrieb Rajarshi Guha:

Hi, is there a built in function that will give me the number of bits
that are set to 1 in a bit string field?

Thanks,

-------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
-------------------------------------------------------------------
Without love intelligence is dangerous;
without intelligence love is not enough.
-- Ashley Montagu

--
email: stefan@yukonho.de
tel : +49 (0)6232-497631
http://www.yukonho.de

On sun, 2007-07-15 at 15:35 -0400, Rajarshi Guha wrote:

Hi, is there a built in function that will give me the number of bits
that are set to 1 in a bit string field?

no, but it should be trivial to do with pl/pgsql

a naive implementation could be:

create or replace function bitsetlen(bit) returns int as $$
declare i int;
c int;
begin
c:=0;
for i in 1..length($1) loop
if substring($1,i,1)=B'1' then
c:=c+1;
end if;
end loop;
return c;
end;
$$ language plpgsql;

gnari

On Jul 15, 2007, at 7:20 PM, Ragnar wrote:

On sun, 2007-07-15 at 15:35 -0400, Rajarshi Guha wrote:

Hi, is there a built in function that will give me the number of bits
that are set to 1 in a bit string field?

no, but it should be trivial to do with pl/pgsql

Thanks for the pointer

-------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
-------------------------------------------------------------------
Gravity brings me down.