SQLite in C and supporting REGEXP

Question

I'm using sqlite3 in C and I'd like to add support for the REGEXP operator. By default, a user defined function regexp() is not present and calling REGEXP will usually result in an error (according to the SQLite pages).

1. How do I add a regexp function to support REGEXP? Presumably I will do this via the sqlite3_create_function call, but I don't know what the application-defined regexp() will look like.

2. Can I use a function from regex.h with sqlite3_create_function and how? Any function I pass to SQLite has to take three arguments of type sqlite3_context*, int, sqlite3_value**. However, the SQLite documents don't seem to explain the meaning of these parameters.

3. Is there sample code for a C regexp() function?

I've not been able to find much on this using Google or the SQLite pages.

Answer 1

You can also try this:

#include <regex.h>

...

void sqlite_regexp(sqlite3_context* context, int argc, sqlite3_value** values) {
    int ret;
    regex_t regex;
    char* reg = (char*)sqlite3_value_text(values[0]);
    char* text = (char*)sqlite3_value_text(values[1]);

    if ( argc != 2 || reg == 0 || text == 0) {
        sqlite3_result_error(context, "SQL function regexp() called with invalid arguments.\n", -1);
        return;
    }

    ret = regcomp(&regex, reg, REG_EXTENDED | REG_NOSUB);
    if ( ret != 0 ) {
        sqlite3_result_error(context, "error compiling regular expression", -1);
        return;
    }

    ret = regexec(&regex, text , 0, NULL, 0);
    regfree(&regex);

    sqlite3_result_int(context, (ret != REG_NOMATCH));
}

...

sqlite3_create_function(*db, "regexp", 2, SQLITE_ANY,0, &sqlite_regexp,0,0)