How is it possible to capture the expression matched with REGEXP operator in sqlite?As an example if we have a query like: select title from foo where title REGEXP '(\d\d)ab?cd', how can we extract the exact expression that has matched the REGEXP operator?
REGEXP is not defined by SQLite, but rather it's a placeholder for a user-defined function. Usually this is provided by sqlite3-pcre which only returns true or false.
You can write your own function to change that, either in C or in the language of your choice. Here's an example of writing your own REGEXP in Perl. Altering pcre.c to return, for example, the first match isn't that difficult. Most of the code is concerned with caching. Instead of passing true or false, you'd pass in the matched substring from pcre_exec to sqlite3_result_text.
I'd suggest writing a new function and leaving REGEXP alone.