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]
----------------------------------------------------------------------------
-
Dennis,
Thanks very much for your help. Works great and is very powerful. That is
exactly what I needed to know.
-Gabe
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
