Gabriel Cook wrote:
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'
Gabe,
The substr() function doesn't work because it stops at the first 0 byte
in the blob, but it is pretty easy to write your own user defined
function that would return a sub blob instead of a sub string using the
C API functions.
Define the function:
void subblob(sqlite3_context* ctx, int argc, sqlite3_value* argv[])
{
const char* blob;
int blen, start, slen, tail;
assert(argc == 3);
blob = (char*)sqlite3_value_blob(argv[0]);
blen = sqlite3_value_bytes(argv[0]);
start = sqlite3_value_int(argv[1]) - 1; /* arg is 1 based */
slen = sqlite3_value_int(argv[2]);
if (start < 0)
start = 0;
tail = blen - start;
if (tail < 0)
tail = 0;
if (tail < slen)
slen = tail;
if (start >= blen)
start = blen - 1;
sqlite3_result_blob(ctx, blob + start, slen, SQLITE_TRANSIENT);
}
Register the function:
int rc = sqlite3_create_function(db, "subblob", 3,
SQLITE_ANY, NULL, subblob, NULL, NULL);
Now you can use the function:
create table t (
id integer primary key,
data blob
);
select id from t where subblob(data, 100, 4) = X'DEADBEEF';
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
