SQLITE, access the matched expression of REGEXP operator

Question

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?

Answer 1

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.

Answer 2

You need one or two sqlite extensions.

If you want the whole matching string, I've had a go at an extension implementing MATCH - on github - which would enable the following query:

select title MATCH '(\d\d)ab?cd' from foo;

This would return the likes of '12acd', '23abcd' etc. However, it looks like you just want the leading digits? In that case my best offer is:

select title MATCH '\d\d' from foo where title REGEXP '\d\dab?cd';

Which would select the relevant lines and extract things like '12', '23' etc.

Source code for the REGEXP extension is at regexp.c

The catch is that you have to compile these yourself with gcc and/or g++, which may involve downloads and learning curves...