How to perform bitwise operations arithmetic in MS

问题:

Inside MSACCESS I want to use relatively simple bitwise operations in WHERE clause of queries such as this:

SELECT *
FROM Table1
WHERE Column1 (some operator) 8 = 0

This would:

• Return rows where Column1 does not have its 4th bit set e.g. 0, 1, 2, ..., 7 (all have their 4th bit clear) and 16 (it is 00010000b)
• Exclude rows where Column1 is 8, 9, 10, ..., 15 etc.

PS: are bitwise operators different from boolean operations?

回答1:

If you can run your query in in ANSI-92 Query Mode (e.g. by changing the Access UI Query Mode or by connecting to it using ADO classic or ADO.NET), use the BAND operator.

The following code sample prints this to the Immediate window:

8 AND 7: -1 
8 BAND 7: 0 

The first case (AND) treats both numbers as True values, so True AND True gives -1 (True). I think the BAND approach is what you're after.

Public Sub BitwiseAndQuery()
    'the db engine treats numbers as booleans with AND '
    Debug.Print "8 AND 7: "; _
        CurrentDb.OpenRecordset("SELECT 8 AND 7")(0)

    'ADO includes BAND for bitwise AND '
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT (8 BAND 7)", CurrentProject.Connection
    Debug.Print "8 BAND 7:"; rs(0)
    rs.Close
    Set rs = Nothing
End Sub

回答2:

you could:

WHERE (((column\(2^b)) mod 2) = 1)

edit: (where b is the specific bit to test)

OR is not available in Access unless you set ANSI Mode.

回答3:

In VBA, you can apply the Boolean operators to numbers in order to perform bitwise operations

(13 AND 8) = 0

In SQL, however, this does not work. However, you could write a VBA function that you call inside a query.