SQLite query with regex

Question

I'm working with Java and I got a db, SQLite 3.8.5, with 2 Columns, ID(int key) and note(string). I want to be able to query for all notes containing multiple words.

Example: Note1: "Hello, this movie is foo"
Note2: "Hello, this movie is yellow"
Note3: "Hello, foo this movie is" (Yoda, don't type!)
Given filter words by user: "foo" "movie"

Result from query should be Note 1 and Note 2.

This regex works but I cant seem to find how ask regex as query: .*(?=.*movie)(?=.*foo).*

BR

score 8 · Answer 1 · edited Aug 25 '19 at 20:57

In addition to the answer above, the solution in Java, for completeness. You'll have to do this for each connection where the REGEXP operator should work.

// Create regexp() function to make the REGEXP operator available
Function.create(connection, "REGEXP", new Function() {
  @Override
  protected void xFunc() throws SQLException {
    String expression = value_text(0);
    String value = value_text(1);
    if (value == null)
      value = "";

    Pattern pattern=Pattern.compile(expression);
    result(pattern.matcher(value).find() ? 1 : 0);
  }
});

score 1 · Answer 2 · edited May 23 '17 at 11:46

SQLite has a REGEXP operator, which you use to compare values. The problem is that by default SQLite does not provide implementation for the regular expresion comparision, so you have to provide one. It's done by implementing the regexp() custom SQL function.

To learn how to implement custom SQL functions, see: Writing user defined SQL functions for SQLite using Java or Groovy?

In other words, you have to tell SQLite how it should perform the regular expression matching, by providing the Java function that does the job.

Once you've implemented the regexp function you can start using REGEXP operator in SQL queries.

SQLite query with regex

2 Answers2

Linked