ORACLE bitwise operators


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: ORACLE bitwise operators

  1. #1
    challis Guest

    ORACLE bitwise operators


    Hi

    I have a small problem finding documentation on oracle bitwise operators
    for creating a query.

    Allow me to elaborate:

    The table looks something like this
    TBL_TEMP
    ID VALUE
    1 4
    2 1
    3 3
    4 7
    5 2
    6 8


    What I am looking for is to use a bitwise operator to check if bit 2 has
    been set (Decimal 2 in this case)

    SELECT * FROM TBL_TEMP WHERE (TBL_TEMP.VALUE XXX 2) = 2;


    The XXX should then be the bitwise AND. and return a result set
    ID VALUE
    3 3
    4 7
    5 2

    similar to VB AND operator

    In SQL server the operator & (BITAND) would be the one to use, I seek the
    corresponding operator for Oracle.

    Hope you can help, thanks in advance

    challis





  2. #2
    Russ Guest

    Re: ORACLE bitwise operators


    "challis" <bmlx@yahoo.com> wrote:
    >
    >Hi
    >
    >I have a small problem finding documentation on oracle bitwise operators
    >for creating a query.
    >
    >Allow me to elaborate:
    >
    >The table looks something like this
    >TBL_TEMP
    >ID VALUE
    >1 4
    >2 1
    >3 3
    >4 7
    >5 2
    >6 8
    >
    >
    >What I am looking for is to use a bitwise operator to check if bit 2 has
    >been set (Decimal 2 in this case)
    >
    >SELECT * FROM TBL_TEMP WHERE (TBL_TEMP.VALUE XXX 2) = 2;
    >
    >
    >The XXX should then be the bitwise AND. and return a result set
    >ID VALUE
    >3 3
    >4 7
    >5 2
    >
    >similar to VB AND operator
    >
    >In SQL server the operator & (BITAND) would be the one to use, I seek the
    >corresponding operator for Oracle.
    >
    >Hope you can help, thanks in advance
    >
    >challis
    >
    >
    >
    >

    challis,
    Oracle has a BITAND function. You will need to use it in conjunction with
    DECODE. ie-

    select *
    from TBL_TEMP
    where DECODE(BITAND(TBL_TEMP.VALUE,2), 2, 2, -1) <> -1;

    HTH,
    -Russ.


  3. #3
    challis Guest

    Re: ORACLE bitwise operators


    "Russ" <russell.thompson@adlink.com> wrote:
    >
    >"challis" <bmlx@yahoo.com> wrote:
    >>
    >>Hi
    >>
    >>I have a small problem finding documentation on oracle bitwise operators
    >>for creating a query.
    >>
    >>Allow me to elaborate:
    >>
    >>The table looks something like this
    >>TBL_TEMP
    >>ID VALUE
    >>1 4
    >>2 1
    >>3 3
    >>4 7
    >>5 2
    >>6 8
    >>
    >>
    >>What I am looking for is to use a bitwise operator to check if bit 2 has
    >>been set (Decimal 2 in this case)
    >>
    >>SELECT * FROM TBL_TEMP WHERE (TBL_TEMP.VALUE XXX 2) = 2;
    >>
    >>
    >>The XXX should then be the bitwise AND. and return a result set
    >>ID VALUE
    >>3 3
    >>4 7
    >>5 2
    >>
    >>similar to VB AND operator
    >>
    >>In SQL server the operator & (BITAND) would be the one to use, I seek the
    >>corresponding operator for Oracle.
    >>
    >>Hope you can help, thanks in advance
    >>
    >>challis
    >>
    >>
    >>
    >>

    >challis,
    >Oracle has a BITAND function. You will need to use it in conjunction with
    >DECODE. ie-
    >
    > select *
    > from TBL_TEMP
    > where DECODE(BITAND(TBL_TEMP.VALUE,2), 2, 2, -1) <> -1;
    >
    >HTH,
    >-Russ.
    >

    Thanks, much appreciated.

    Two more questions on this ....

    1. How efficient is this query now whith respect to an InStr. ie how does
    it compare to using characters instead of bits as flags?

    2. Are there functions to return bitwise OR and XOR?

    challis


  4. #4
    Russ Guest

    Re: ORACLE bitwise operators


    "challis" <bmlx@yahoo.com> wrote:
    >Thanks, much appreciated.
    >
    >Two more questions on this ....
    >
    >1. How efficient is this query now whith respect to an InStr. ie how does
    >it compare to using characters instead of bits as flags?
    >
    >2. Are there functions to return bitwise OR and XOR?
    >
    >challis
    >


    Sorry challis, I don't have any statistics on how this compares to instr().


    Oracle does not have any intrinsic BITOR or BITXOR functions. However, with
    some boolean algebra you can create your own to extend SQL.


    http://www.oracledba.co.uk/tips/bitwise_ops.htm


    SQL> select BITOR(4,1) from dual;

    BITOR(4,1)
    ----------
    5

    SQL> select BITXOR(7,1) from dual;

    BITXOR(7,1)
    -----------
    6

    HTH,
    -Russ.



  5. #5
    challis Guest

    Re: ORACLE bitwise operators


    Thanks Russ

    challis


    "Russ" <russell.thompson@adlink.com> wrote:
    >
    >"challis" <bmlx@yahoo.com> wrote:
    >>Thanks, much appreciated.
    >>
    >>Two more questions on this ....
    >>
    >>1. How efficient is this query now whith respect to an InStr. ie how does
    >>it compare to using characters instead of bits as flags?
    >>
    >>2. Are there functions to return bitwise OR and XOR?
    >>
    >>challis
    >>

    >
    >Sorry challis, I don't have any statistics on how this compares to instr().
    >
    >
    >Oracle does not have any intrinsic BITOR or BITXOR functions. However,

    with
    >some boolean algebra you can create your own to extend SQL.
    >
    >
    > http://www.oracledba.co.uk/tips/bitwise_ops.htm
    >
    >
    >SQL> select BITOR(4,1) from dual;
    >
    >BITOR(4,1)
    >----------
    > 5
    >
    >SQL> select BITXOR(7,1) from dual;
    >
    >BITXOR(7,1)
    >-----------
    > 6
    >
    >HTH,
    >-Russ.
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center