Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Bitwise AND operator in Oracle PL/SQL

Posted on 2000-04-06
9
Medium Priority
?
6,041 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 400 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

916 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