Comparing bit in an integer field

2009/11/16 Amitabh Kant <amitabhkant@gmail.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

In response to Amitabh Kant :

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.

test=# select * from t1;
a1
----
0
12
8
0
1
10
7
19
(8 rows)

test=*# select a1 from t1 where (a1>>2)::bit = B'1';
a1
----
12
7
(2 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

2009/11/16 A. Kretschmer <andreas.kretschmer@schollglas.com>:

In response to Amitabh Kant :

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.

test=# select * from t1;
 a1
----
 0
 12
 8
 0
 1
 10
 7
 19
(8 rows)

test=*# select a1 from t1 where (a1>>2)::bit = B'1';
 a1
----
 12
 7
(2 rows)

Ah, bit-shifting. Told you there'd be a cleaner way ;)

On Mon, Nov 16, 2009 at 7:53 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:

In response to Amitabh Kant :

test=# select * from t1;
a1
----
0
12
8
0
1
10
7
19
(8 rows)

test=*# select a1 from t1 where (a1>>2)::bit = B'1';
a1
----
12
7
(2 rows)

Regards, Andreas

Thanks. That should solve my problem.

Amitabh