Finding a row with a binary status

[djdehaan]

Hi,

I use in an SQL table an Integer column called 'Caps' to store capabilities. Each bit of the integer represent a capability. Now I need to select in an SQL statement only those rows that have a specific capability. And i cant find how to do this! Someone knows?

Assume
-row 1 of the table has Caps 4
-row 2 of the table has Caps 5
-row 3 of the table has Caps 3

This is what i tried and did NOT work (assume I look for the 3rd bit, I only want rows 1 and 2):

SELECT * FROM table WHERE Caps AND 0x4 = 0x4
or
SELECT * FROM table WHERE Caps & 0x4 = 0x4
or
SELECT * FROM table WHERE oct(Caps) & 0x4 = 0x4

Anyone has a suggestion?

 

[PHV]

And this ?
SELECT * FROM table WHERE (Caps AND 4) = 4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[djdehaan]

No, does not work with me (Access 2003). Zero records returning. It looks like Access evaluates 'Caps' as a boolean and also 4 (although all are Numbers).

 

[PHV]

In a standard code module create a function like this:

Public Function myAnd(Arg1, Arg2)
If Not IsNull(Arg1) And Not IsNull(Arg2) Then myAnd = (Val(Arg1) And Val(Arg2))
End Function

Now you can try this:
SELECT * FROM table WHERE myAnd([Caps], 4) = 4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

If SQL supports bit conversions, create a pass-through query and use the appropriate keywords.
Access does not play well with bits. (Although you could probably write a function to do so).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[djdehaan]

Hi All,

Thanks for your thinking. I prefer not to include Access code (it is a .net web app). I now have a flex way to do it, as follows:

WHERE (((Fix(([Caps]-(Fix([Caps]/B))*B)/A)=1)))

A is the num value of the bit i look for
and B is the num value of the next bit

 

[traingamer]

It seems like an awful lot of work when you could just have several more boolean fields in your table.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill