How can I create a user-defined function in SQLite?

by

SQLite does not have support for user-defined functions in the way that Oracle or MS SQL Server does. For SQLite, you must create a callback function in C/C++ and hook the function up using the sqlite3_create_function call.

Unfortunately, the SQLite API for Android does not allow for the sqlite3_create_function call directly through Java. In order to get it to work you will need to compile the SQLite C library with the NDK.

And if you are still interested read 2.3 User-defined functions…

Here’s how to create a function that finds the first byte of 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);
}

Then attach the function to the database.

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

Finally, use the function in a sql statement.

SELECT firstchar(textfield) from table

More Related Contents:

  1. Android SQLite data display to ListView
  2. How to retrieve and display data from a database to TextView
  3. IN clause and placeholders
  4. Android Database Transaction
  5. SQLiteDatabase.query method
  6. About “_id” field in Android SQLite
  7. Import .csv file to Sqlite in Android
  8. Debugging sqlite database on the device
  9. SQLite Android Database Cursor window allocation of 2048 kb failed
  10. How to insert a SQLite record with a datetime set to ‘now’ in Android application?
  11. Android SQLite: Update Statement
  12. How to delete SQLite database from Android programmatically
  13. Bulk Insertion on Android device
  14. Android Sqlite selection args[] with int values
  15. problem about sqlite database, no such table:
  16. SQLite simultaneous reading and writing
  17. update sql database with ContentValues and the update-method
  18. SQLiteDatabase close() function causing NullPointerException when multiple threads
  19. Using the LIMIT statement in a SQLite query
  20. sqlite encryption for android
  21. Android quotes within an sql query string
  22. Retrieve large blob from Android sqlite database
  23. Android SQLite rawquery parameters
  24. Saving ArrayLists in SQLite databases
  25. How to access an existing sqlite database in Android?
  26. How to convert milliseconds to date in SQLite
  27. DB File in Assets Folder. Will it be Updated?
  28. Backup/restore sqlite db in android [duplicate]
  29. Compare dates (stored as string) in android sqlite database?
  30. Backup Room database

Leave a Comment