L'Altro Wiki/ informatica/ soft/ sqlite3-pcre

 

sqlite3-pcre: regular expressions with sqlite3 under Debian

Preface

The REGEXP operator is a special syntax for the regexp() user function.
No regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If a application-defined 
SQL function named "regexp" is added at run-time, that function will be called
in order to implement the REGEXP operator.

This is what you can read at http://www.sqlite.org/lang_expr.html#regexp

So, if you want to be able to use the regexp() user function while doing queries to your sqlite3 database, you need sqlite3-pcre.

Outcome

Under any version of Debian OS - stable, testing or sid - the "sqlite3-pcre" package is currently not available (06-10-2012), so you have to compile it.

Library Requirements:

# apt-get install libpcre3-dev libpcre3 libsqlite3-dev

Compiling sqlite3-pcre

$ git clone https://github.com/oojah/sqlite3-pcre.git
$ cd sqlite3-pcre
$ make

Now you can make the new library available system wide - copying it to some path like "/usr/lib/sqlite3/pcre.so", or like "/usr/local/lib/sqlite3/pcre.so" - or just for your user, leaving it in your home directory.

To be able to use it, you have to load it each time you open the database:

.load /usr/lib/sqlite3/pcre.so

Or you could put that line into your ~/.sqliterc

Doing a SELECT query using "REGEXP" operator will be something like:

SELECT column FROM table WHERE column REGEXP '<here goes your expression>';