Android - How to use REGEX in RawQuery in Room database?

Question

I am unable to find solution to seemingly very simple problem. I need to execute a raw query in Room database but the query has REGEX function in it. So, I tried to write it like we usually do with the LIKE keyword:

val query = "SELECT * FROM some_table WHERE some_column REGEX '(^| )('||?||')( |$)'"

Similar to how we use LIKE keyword:

REGEX '(^| )(' ||?|| ')( |$)'

LIKE '%' ||?|| '%'

And afterwards:

tableDao.search(
    SimpleSQLiteQuery(query, arrayOf("believe"))
)

But it doesn't return anything, whereas if I manually execute this query in DB Browser for SQLite, it returns exactly what I'm expecting.

What am I doing wrong here?

Answer 1

I don't believe that the regex function is defined in the standard Android versions of SQLite and hence the issue.

as per :-

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)".

• https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators