Bitwise comparison

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Bitwise comparison

de f
What's the best way to do a bitwise comparison in a sql query
such as the one indicated below - for example - i have one
Indicator field (currently defined as numeric) in my table which
is being used to store a variety of indicators (rather than
having a slew of boolean columns)

eg (random examples):

byte 1 indicates IsRetired (val 1)
byte 2 indicates IsOnDisability (val 2)
...
byte 4 indicates IsOverSixFeetTall (val 8)

ex: if the Indicator value = 9 (byte 1001) then IsRetired = true
,IsOnDisability = false and IsOverSixFeetTall = true

In vb - to determine whether a hex flag is on or not, we do a
bitwise comparison such as

IsRetired = (Indicator and 1)
IsOnDisability = (Indicator and 2)
IsOverSixFeetTall = (Indicator and 8)

How do I mimic this sort of behavior in sqlite?

For example  -

Select * from MyTable Where (Indicator and 1)

Any help is appreciated.

________________________________________________
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag
Reply | Threaded
Open this post in threaded view
|

Re: Bitwise comparison

Martin Engelschalk
Hello Debra,

as far as i know, SQL standard does not have an operator or function
that does bitwise comparison, and sqlite surely does not.
However, you can add a function using sqlite3_create_function (See
http://www.sqlite.org/capi3ref.html#sqlite3_create_function) that does this,
and then use this function (i naed it 'IsIndicator') in your query:

Select * from MyTable Where IsIndicator(Indicator, 1) = 1

Martin

debra f schrieb:

>What's the best way to do a bitwise comparison in a sql query
>such as the one indicated below - for example - i have one
>Indicator field (currently defined as numeric) in my table which
>is being used to store a variety of indicators (rather than
>having a slew of boolean columns)
>
>eg (random examples):
>
>byte 1 indicates IsRetired (val 1)
>byte 2 indicates IsOnDisability (val 2)
>...
>byte 4 indicates IsOverSixFeetTall (val 8)
>
>ex: if the Indicator value = 9 (byte 1001) then IsRetired = true
>,IsOnDisability = false and IsOverSixFeetTall = true
>
>In vb - to determine whether a hex flag is on or not, we do a
>bitwise comparison such as
>
>IsRetired = (Indicator and 1)
>IsOnDisability = (Indicator and 2)
>IsOverSixFeetTall = (Indicator and 8)
>
>How do I mimic this sort of behavior in sqlite?
>
>For example  -
>
>Select * from MyTable Where (Indicator and 1)
>
>Any help is appreciated.
>
>________________________________________________
>Get your own "800" number
>Voicemail, fax, email, and a lot more
>http://www.ureach.com/reg/tag
>  
>
Reply | Threaded
Open this post in threaded view
|

Re: Bitwise comparison

Dennis Cote
In reply to this post by de f
debra f wrote:

>What's the best way to do a bitwise comparison in a sql query
>such as the one indicated below - for example - i have one
>Indicator field (currently defined as numeric) in my table which
>is being used to store a variety of indicators (rather than
>having a slew of boolean columns)
>
>eg (random examples):
>
>byte 1 indicates IsRetired (val 1)
>byte 2 indicates IsOnDisability (val 2)
>...
>byte 4 indicates IsOverSixFeetTall (val 8)
>
>ex: if the Indicator value = 9 (byte 1001) then IsRetired = true
>,IsOnDisability = false and IsOverSixFeetTall = true
>
>In vb - to determine whether a hex flag is on or not, we do a
>bitwise comparison such as
>
>IsRetired = (Indicator and 1)
>IsOnDisability = (Indicator and 2)
>IsOverSixFeetTall = (Indicator and 8)
>
>How do I mimic this sort of behavior in sqlite?
>
>For example  -
>
>Select * from MyTable Where (Indicator and 1)
>
>Any help is appreciated.
>
>________________________________________________
>Get your own "800" number
>Voicemail, fax, email, and a lot more
>http://www.ureach.com/reg/tag
>
>  
>
Debra,

You need the bitwise operators & (and), | (or), and ~ (not).

IsRetired = (Indicator & 1)
IsOnDisability = (Indicator & 2)
IsOverSixFeetTall = (Indicator & 8)

These values will be non-zero if the condition is true.

Select * from MyTable Where Indicator & 1;

You use the | operator to set the condition and & with the complement to clear.

Update MyTable
set Indicator = Indicator | 1, -- set IsRetired
set Indicator = Indiocator & ~2 -- clear IsOnDisability
where Indicator & 8

HTH
Dennis Cote