Re: Comparing bit in an integer field

2009/11/16 Amitabh Kant <amitabhkant(at)gmail(dot)com>:
> Hello
>
> I need to compare the bit values of a integer field in my table. For
> example, I have a table called "t1" with just one field "a1" having
> following values:
>
> a1
> ==
> 0
> 12
> 8
> 0
> 1
> 10
> 7
> 19
>
> I am trying to fetch all records where the 3rd binary bit is 1, which from
> the above example should be 12 (00001100) and 7 (00000111). If I convert the
> values to binary and store it as string, I can easily compare them using
> substr, but would rather like to retain the integer field. I have tried
> using the get_bit function, but it seems my understanding of the function is
> not correct.
>
> I would appreciate any help in this regard.
>
>
> Amitabh
>

There's probably a clean way of doing this, but you could do:

SELECT a1
FROM t1
WHERE (a1::bit(10) & 4::bit(10))::int = 4;

a1
----
12
7
(2 rows)

Regards

Thom