Re: Binary Operation on integer

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/18
Message-ID: <350fbcb5.18669426_at_www.sigov.si>#1/1

On Wed, 18 Mar 1998 09:49:28 +0100, Gerard Le Comte <lecomte_at_sns.fr> wrote:

>This is a multi-part message in MIME format.
>--------------D05B209452C74C6F124A2FC5
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>Hi,
>
> I have a table in my Oracle Database that owns a value, an integer.
>This value in my Pro*C prog can take some of these values :
>#define BIT_1 1
>#define BIT_2 2
>#define BIT_3 4
>#define BIT_4 8
>#define BIT_5 16 ....
>
> So, i can have to rows, one with a value of 1+8=9 and another with 1+4=5.
>Now, i want to SELECT the rows which have the bit BIT_3 up.
>
> Has anyone a simple solution who to store this value (integer, raw??),
>and who to do this SELECT statement?
> Sure, it is possible to compute with a function : value/3 and test the parity,
>but it is not very pretty nor very speedy!
>
> Thanks for anyone that could reply with a good idea!

There is undocumented (!?) function BITAND in Oracle, which is exactly what you need. This function takes two numerical arguments and performs binary AND operation on them (actualy, on their integer parts, if they are decimal numbers).

For example, SELECT BITAND(15,6) FROM DUAL will return number 6,

             SELECT BITAND(13,6) FROM DUAL will return number 4,....

So for your example, use:

   SELECT * FROM your_table WHERE BITAND(your_column,:BIT_3) != 0;

This BITAND function is available at least from release 7.2 on, I'm not sure about previous versions.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Mar 18 1998 - 00:00:00 CET