Re: BITWISE AND

In article <3icgko$e15_at_pheidippides.axion.bt.co.uk> Karl Zdero <karlz_at_pst.bt.co.uk> writes:

> 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, it sort of violates the whole idea of a relational database to use bitwise encoded values in columns... but there is actually an undocumented (?) SQL function called bitand(number,number), which does what you want to achieve. I think it's actually used in one of the dictionary views (that's where I found it).

With Oracle 7.1 or higher, you can of course easily implement and call your own PL/SQL functions from SQL statements, enabling you to write bitand(), bitor(), bitxor() etc etc for numeric or character data or whatever you choose.    

--

Magnus Lonnroth			E-mail: mloennro_at_se.oracle.com
Product Manager	Europe		Visit : Molndalsvagen 91, Goteborg, Sweden
Internet/Web Products		Office: +46-31-830325 Mobile: +46-70-5916325
Network Products Division	Web   : http://ubik.se.oracle.com (firewall)
Oracle Corp.			Sales, Oracle Sweden