I try to get a part of a BLOB field in a select query, e.g.

subblobstr(BLOB_DATA,2,3)

The code is as follows, and it can be compiled correctly, but it doesn't work: subblobstr(BLOB_DATA,2,3) still gets the same result just like substr(BLOB_DATA,2,3).

#include 
#include 
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
    
void subblobstr(sqlite3_context* ctx, int argc, sqlite3_value* argv[])
{
    const char* blobdata;
    int blob_len, start, slen;
   
    blobdata = (char*)sqlite3_value_blob(argv[0]);
    blob_len = sqlite3_value_bytes(argv[0]);
    start = sqlite3_value_int(argv[1]) - 1; 
    slen = sqlite3_value_int(argv[2]);
    sqlite3_result_blob(ctx, blobdata + start, slen, SQLITE_TRANSIENT);
}
    
int sqlite3SubBlobStrInit(sqlite3 *db){
    sqlite3_create_function(db, "subblobstr",3,SQLITE_ANY, 0, subblobstr, 0, 0);
    return 0;
}
    
int sqlite3_extension_init(
    sqlite3 *db, 
    char **pzErrMsg,
    const sqlite3_api_routines *pApi
){
    SQLITE_EXTENSION_INIT2(pApi)
    return sqlite3SubBlobStrInit(db);
}

The structure of table:

BLOB_DATA is a blob type field, which contains a single 20MB string, and I just want to display the first few bytes of the string, however, substr function doesn't work because it will stop at the first NULL byte in the BLOB_DATA, so I have to write the function above to solve the problem.

The table contains lots of data, but substr(BLOB_DATA,1,10) returns a single digit at most.

Quote from CL:

that the values in the table are strings, in which case you should convert them into blobs with something like this:
UPDATE MyTable SET blob_data = CAST(blob_data AS BLOB)

After running SQL statement above, sqlite shell will crash. (SQLite version 3.7.15.2 Win7)
I use SQLite Database, but Browser to set the type of BLOB_DATA first (BLOB), then substr(BLOB_DATA,1,10) still return 1 digit.