Solved

Bitwise AND operator in Oracle PL/SQL

Posted on 2000-04-06
9
5,654 Views
Last Modified: 2012-06-21
Is there any bitwise AND operator in Oracle  (SQL,  PL/SQL)?
If not, what can I do to evaluate if a bit in an integer is set?

Example:
myint = 9 (bitwise = 1001)
How can I check to see if the second rightmost bit is set or not?


Sagres.
0
Comment
Question by:Sagres
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 4

Accepted Solution

by:
syakobson earned 100 total points
ID: 2690867
PL/SQL provides function BITAND(a,b) where a and b are numbers. To check if second rightmost bit is set issue BITAND against your number and number 2. If result is 2, then bit is set:

SQL> begin
  2  if BITAND(&1,2) = 2
  3  then
  4  dbms_output.put_line('Bit is set');
  5  else
  6  dbms_output.put_line('Bit is not set');
  7  end if;
  8  end;
  9  /
Enter value for 1: 9
old   2: if BITAND(&1,2) = 2
new   2: if BITAND(9,2) = 2
Bit is not set

PL/SQL procedure successfully completed.

SQL> /
Enter value for 1: 10
old   2: if BITAND(&1,2) = 2
new   2: if BITAND(10,2) = 2
Bit is set

PL/SQL procedure successfully completed.

SQL>

Solomon Yakobson

0
 
LVL 4

Expert Comment

by:syakobson
ID: 2690892
You can also do it in sql using MOD and TRUNC and POWER functions. To extract N'th bit from a number use:

SELECT TRUNC(MOD(X,POWER(2,N))/2) FROM dual;

where X is the number:

SQL> SELECT TRUNC(MOD(&1,POWER(2,&2))/2) FROM dual
SQL> /
Enter value for 1: 9
Enter value for 2: 2
old   1: SELECT TRUNC(MOD(&1,POWER(2,&2))/2) FROM dual
new   1: SELECT TRUNC(MOD(9,POWER(2,2))/2) FROM dual

TRUNC(MOD(9,POWER(2,2))/2)
--------------------------
                         0

SQL> /
Enter value for 1: 10
Enter value for 2: 2
old   1: SELECT TRUNC(MOD(&1,POWER(2,&2))/2) FROM dual
new   1: SELECT TRUNC(MOD(10,POWER(2,2))/2) FROM dual

TRUNC(MOD(10,POWER(2,2))/2)
---------------------------
                          1

SQL>

Solomon Yakobson.

0
 
LVL 4

Expert Comment

by:dda
ID: 2690902
You can also take a look at UTL_RAW package ($ORACLE_HOME/rdbms/admin/utlraw.sql). It has a number of bit-level functions.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:jaramill
ID: 2691089
BITAND looks much simpler than the UTL_RAW.BIT_AND function.  That one requires you to put in the string in bit form "1001".  With BITAND by itself you just put in the number and it works.  Solomon's solution seems simple.

Gio
0
 
LVL 4

Expert Comment

by:syakobson
ID: 2691478
Yes, BITAND is simpler in case you work with numbers. Recently, in one of the previous questions I suggested to use UTL_RAW package since the question was not just bitwise AND but also OR and NOT. I have no clue why Oracle implemented BITAND but not BITOR, BITXOR, BITNOT. Maybe because they can be relatively easy derived from BITAND?

Solomon Yakobson.
0
 
LVL 3

Expert Comment

by:jaramill
ID: 2691487
But I've never heard of BITAND.  I don't even see it in the documentation.  Where can I find documentation for BITAND Solomon?

Thanks,
Gio
0
 
LVL 4

Expert Comment

by:dda
ID: 2692029
jaramill:

Next time try searching the usenet like this:
http://www.deja.com/dnquery.xp?ST=MS&QRY=oracle+sql+bitwise+function&svcclass=dnserver&DBS=2

:)

Dmitry.
0
 

Author Comment

by:Sagres
ID: 2692666
Bitand works fine, it is what we were looking for.
0
 
LVL 4

Expert Comment

by:syakobson
ID: 2693366
BITAND is part of package STANDARD. It a special package, "heart" of PL/SQL if you will. You can find it in $ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL

Solomon Yakobson.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question