How do I use regex in a SQLite query?

Question

I'd like to use a regular expression in sqlite, but I don't know how.

My table has got a column with strings like this: "3,12,13,14,19,28,32" Now if I type "where x LIKE '3'" I also get the rows which contain values like 13 or 32, but I'd like to get only the rows which have exactly the value 3 in that string.

Does anyone know how to solve this?

Answer 1

As others pointed out already, REGEXP calls a user defined function which must first be defined and loaded into the the database. Maybe some sqlite distributions or GUI tools include it by default, but my Ubuntu install did not. The solution was

sudo apt-get install sqlite3-pcre

which implements Perl regular expressions in a loadable module in /usr/lib/sqlite3/pcre.so

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.

Now you can query like this:

SELECT fld FROM tbl WHERE fld REGEXP '\b3\b';

If you want to query directly from the command-line, you can use the -cmd switch to load the library before your SQL:

sqlite3 "$filename" -cmd ".load /usr/lib/sqlite3/pcre.so" "SELECT fld FROM tbl WHERE fld REGEXP '\b3\b';"

If you are on Windows, I guess a similar .dll file should be available somewhere.