I want to run a query in SQLite with a regexp using Android.
How do I do that?
Add a comment
|
3 Answers
Unfortunately it doesn't look like Android currently supplies a way for you to inject a user function into sqlite, which is what you'd need to make REGEXP work (specifically, you'd need a user function named regexp()) -- so you may have to select more broadly (with a LIKE condition that's broader than the regexp you want) and further distinguish "actual" result (those that really match the regexp you want) with java.util.regex in your application.
Comments
Very late to the party here, but you can use REGEXP in Room - you just have to enable it first. Firstly, in your @Dao interface, set up a @RawQuery function (in Kotlin):
@Dao
interface WordsDao {
@RawQuery
fun getMatches(query: SimpleSQLiteQuery): List<Word>
@RawQuery
fun enableRegexp(query: SimpleSQLiteQuery): Boolean
}
Then you need to call it with the query to enable REGEXP:
private fun enableRegexp() {
Single.fromCallable {
appDB?.wordsDao()?.enableRegexp(SimpleSQLiteQuery("PRAGMA compile_options='ENABLE_REGEXP'"))
}.doOnSuccess {
Log.d("REGEXP", "Success")
}.subscribeOn(Schedulers.io())
.observeOn(AndroidSchedulers.mainThread())
.subscribe()
}
Now you can run a SELECT query on the database using regex:
appDB?.wordsDao()?.getMatches(SimpleSQLiteQuery("SELECT * FROM words_alpha WHERE word REGEXP '^[olgf]{4}+\$'"))
4 Comments
sfinja
I am afraid this approach wouldn't work as intended. The issue is that SQLite in Android doesn't have built-in
REGEXP support, and PRAGMA compile_options can't enable it since it's not compiled into Android's SQLite binary. The ENABLE_REGEXP pragma you're trying to use is something that's available in some SQLite builds, but not in Android's default SQLite implementation. How and where did you make yours work?
Computersmith64
@sfinja - the example I provided is exactly the code I used in a test app I wrote in Android Studio to try out some concepts for a possible word game. If I run that code on my SQLite database containing 10,000 words I get a log output "REGEXP Success" & a list of 3 words (fool, golf & logo). Using Room 2.6.1
Computersmith64
@sfinja - additionally, the SQLite database master was created using DB Browser for SQLite & added to the project's Assets folder.
sfinja
Amazing. It appears that a lot has changed since 2022: stackoverflow.com/a/73944066/2946787 This is indeed good news. Thank you.
Patterns are compiled regular expressions. In many cases, convenience methods such as String.matches, String.replaceAll and String.split will be preferable, but if you need to do a lot of work with the same regular expression, it may be more efficient to compile it once and reuse it. The Pattern class and its companion, Matcher, also offer more functionality than the small amount exposed by String.
http://developer.android.com/reference/java/util/regex/Pattern.html
