How to use regexp in sqlite

Question

Please prompt, how used REGEXP in SQLite?

Realization:

SELECT field FROM table WHERE field REGEXP '123'

It is not working. Error: no such function: REGEXP

Answer 1

Currently am working on sqlite3+php for my web application in Ubuntu 12.04. I was also faced the same issue then after research i had found that we need to install one pcre package for sqlite3. Here we go how to

1. apt-get install sqlite3-pcre
2. Then go to sqlite3 command line sqlite3 test.db
3. run the command .load /usr/lib/sqlite3/pcre.so
4. test : SELECT url FROM table_name where url REGEXP '^google.*';

After following those 3 steps its working fine for me. If am wrong regarding this please correct me. I hope it will help you

Thanks

Answer 2

You can't use it.

First, you'd need a function that implements it, as per documentation:

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 an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

There's no way to install user-defined functions in Android SQLite.

Consider whether you can write your regex as a GLOB or LIKE pattern instead.