Bitwise AND operator in Oracle PL/SQL

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.
SagresAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
syakobsonConnect With a Mentor Commented:
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
 
syakobsonCommented:
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
 
ddaCommented:
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jaramillCommented:
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
 
syakobsonCommented:
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
 
jaramillCommented:
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
 
ddaCommented:
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
 
SagresAuthor Commented:
Bitand works fine, it is what we were looking for.
0
 
syakobsonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.