Re: BITWISE AND

From: Joseph Bennett <bennettj_at_news-server>
Date: 23 Feb 1995 13:35:37 GMT
Message-ID: <3ii2v9$4sj_at_nuclear.microserve.net>

Karl Zdero (karlz_at_pst.bt.co.uk) wrote:
: I have a conversion task to perform. One of the fields contains a
: series of 1's and 0's ie: 10011011.
:
: This field represents a number of flags that are turned on!
 

: Now, I could store this field as a numeric column (in this case the
: value would be 155 as bitpos 1,4,5,7 and 8 are turned on.
 

: How would I in SQL, determine which flags are turned on in a SELECT
: statement?
 

: Something along the lines of a BITWISE AND operation.
: ie: To determine if flag number 4 is turned on I would AND the colums
: value with 4 and test the result is 4.
 

: As far as I know, BITWISE ops are not possible in SQL.

Well, there is an undocumented function in Oracle (was in 6 and is in 7) called BITAND which may do what you need. In you example, you could have a numeric column COLN in table TABA and the test would be as follows:

   SELECT BITAND(COLN,1) FROM TABA;    ** Check for Bit 1
   SELECT BITAND(COLN,2) FROM TABA;    ** Check for Bit 2
   SELECT BITAND(COLN,4) FROM TABA;    ** Check for Bit 3

   .....
   SELECT BITAND(COLN,128) FROM TABA; ** Check for BIT 8

In your example of using 155 for COLN, then the SELECTs that check for BITS 1, 2, 4, 5, and 8 would return non-zero values, while the others would return 0. This correctly indicates the bits for 155.

Have fun with Oracle - they do!  

--

      +------------+
     /| +--------+ |
    //| |      //| | Joseph P. Bennett
   //|| |_____///| | Systems Consultant
  //_|| |_____// | | bennettj_at_ugly.microserve.net
  ||_|| |____ ||_| |
  || ||     | ||   |
  || /+-----| ||---+ "There are a lot of dumb people with powerful tools."
  ||// _____| ||/ /  Jonah Seiger, 1994
  | / /_______|  /
  |/__________| /

Received on Thu Feb 23 1995 - 14:35:37 CET