Using Bitwise Logic In Query

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

Using Bitwise Logic In Query

Dave Blake
Looking for the best way to query a table with an integer column by value
of the lower 16 bits of the data in that column. Does SQLite support
bitwise logic?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using Bitwise Logic In Query

Simon Slavin-3

On 8 Sep 2016, at 11:07am, Dave Blake <[hidden email]> wrote:

> Looking for the best way to query a table with an integer column by value
> of the lower 16 bits of the data in that column. Does SQLite support
> bitwise logic?

Yes.  You can use the following

& | ~ << >>

AND OR NOT SHIFTLEFT SHIFTRIGHT

However it is far faster to search for integer values.  So if your table has lots of rows and/or you do lots of queries like this, you should consider keeping a copy of the lower 16 bits as another integer column.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using Bitwise Logic In Query

Paul Sanderson
In reply to this post by Dave Blake
How does this work for you to get all the even rows

SELECT ROWID
FROM table
WHERE ROWID & 0x01 = 0x00


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 8 September 2016 at 11:07, Dave Blake <[hidden email]> wrote:
> Looking for the best way to query a table with an integer column by value
> of the lower 16 bits of the data in that column. Does SQLite support
> bitwise logic?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using Bitwise Logic In Query

Clemens Ladisch
In reply to this post by Simon Slavin-3
Simon Slavin wrote:
> ... if your table has lots of rows and/or you do lots of queries like
> this, you should consider keeping a copy of the lower 16 bits as
> another integer column.

In these case, you should consider using an expression index:

sqlite> create table t(x,y);
sqlite> create index tx on t(x & 0xffff);
sqlite> .eqp on
sqlite> select * from t where x & 0xffff = 42;
--EQP-- 0,0,0,SEARCH TABLE t USING INDEX tx (<expr>=?)


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using Bitwise Logic In Query

Matthias-Christian Ott
In reply to this post by Paul Sanderson
On 2016-09-08 12:17, Paul Sanderson wrote:
> How does this work for you to get all the even rows
>
> SELECT ROWID
> FROM table
> WHERE ROWID & 0x01 = 0x00

I think you should be able to create an expression index as well:

https://www.sqlite.org/lang_createtable.html#rowid

- Matthias-Christian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users