How to use regular expressions with sqlite query?

[realme0213]

I didn't find the relevant documentation about how to use regular expressions with sqlite query, is it supported by Sea-ORM?

[billy1624]

Hey @realme0213, sorry for the delay. You can use sea_query::Expr::cust method to construct a custom regular expressions.

[realme0213]

Hi, @billy1624 thank you for your reply, I use sea_query::Expr::cust method to build my sqlite query

blog::Entity::find()
    .filter(Expr::col(blog::Column::UserId).eq(user_id))
    .filter(Expr::cust(r#""content" REGEXP "text""#))

also, I log raw sqlite string

SELECT "blogs"."id", "blogs"."user_id", "blogs"."content", "blogs"."title" FROM "blogs" WHERE "user_id" = 'd7f8d349-c44e-4cfa-aa9a-c085f3b0f110' AND "content" REGEXP "text"

then, I have a error message:

Query Error: error returned from database: no such function: REGEXP

I found sea-orm relying on SQLx, and SQLx doesn't seem to support sqlite REGEXP, launchbadge/sqlx#194

Here is some informations about sqlite REGEXP from sqlite document

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

So, is there any other workaround to support regular expressions with sea-orm sqlite query