If you can get this to work it will be very slow. I suggest when writing to this table that you extract the relevant parts of the data blob and store them in separate fields with an appropriate index on each each . This will avoid the a table scan for every query, which is what you'd get if your example worked.
RDBMS are designed to allow you to query data in a flexible format. Trying to extract pieces of data from a blob is not what they're meant to do, you need to normalize your data before you can query it effectively. On 11/3/06, Gabriel Cook <[EMAIL PROTECTED]> wrote:
Hello all, I'm trying to figure out if there is a way use portions of a BLOB field in a select query. Here is an example of the table: CREATE TABLE fcdata ( timestamp INTEGER NOT NULL, port INTEGER NOT NULL, dataelementtype INTEGER NOT NULL, iserror INTEGER DEFAULT 0, length INTEGER DEFAULT NULL, data BLOB DEFAULT NULL ) ; Which has an index, as follows: CREATE INDEX fcdata_timestamp_port ON fcdata (timestamp, port); I'm also depending on Sqlite to generate the rowid automatically. I'd really like to do something like the following (which by the way, doesn't work :) ) SELECT rowid, timestamp, port, dataelementtype, iserror, length, data, FROM fcdata WHERE substr(data, 1, 1) == x'bc' ORDER BY timestamp, port LIMIT 1000 OFFSET 0; Is there any way to filter by a byte position within the BLOB? Any help is appreciated. Thanks very much for your time. -Gabe ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
-- Isaac Raway Entia non sunt multiplicanda praeter necessitatem. http://blueapples.org - blog http://stonenotes.com - personal knowledge management
