Thanks for your reply. However, I can't normalize the data in the blob, basically, its an arbitrary frame of data. I don't know what's in it or how its formatted at design time.
I'm ok with the table scan, IF I there is any way I can get the query to work. I'll have to scan the data even if I move it back into a flat file. BTW I understand what your saying about normalizing the data, and in general I agree with you. So, is there any way to of thing with a BLOB? > WHERE substr(data, 1, 1) == x'bc' Thanks, Gabe -----Original Message----- From: Isaac Raway [mailto:[EMAIL PROTECTED] Sent: Friday, November 03, 2006 9:48 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Using BLOBs in where fields 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 ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
