To add a custom function to SQLite

SQLite does not support user-defined functions that Oracle or MS SQL Server provides. In SQLite, you must create a callback function with C/C++ and connect it using sqlite3_create_function.

Unfortunately, the SQLite API provided by Android does not support sqlite3_create_function directly from Java. Therefore, you must compile the SQLite C library using NDK.

Anyway, if you're interested in custom function...

Let me show you how to add a function that finds the first character in a string.

static void firstchar(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    if (argc == 1) {
        char *text = sqlite3_value_text(argv[0]);
        if (text && text[0]) {
          char result[2]; 
          result[0] = text[0]; result[1] = '\0';
          sqlite3_result_text(context, result, -1, SQLITE_TRANSIENT);
          return;
        }
    }
    sqlite3_result_null(context);
}

The function is then added to the database.

sqlite3_create_function(db, "firstchar", 1, SQLITE_UTF8, NULL, &firstchar, NULL, NULL)

This allows the function to be used as shown below.

SELECT firstchar(textfield) from table