no such function: REGEXP

[fg5002]

Hello,

Many thanks for this great library! It might be a lame question and unrelated to the SQLocal, but I can't figure out how to make the REGEXP operator work. My queries return with 'no such function: REGEXP.' error. Thank you in advance for your help.

[DallasHoff]

Can you post the query you are trying to run?

[fg5002]

Thanks for your quick reply.

const data = await sql`select hun, ltn from taxons where lower(hun) REGEXP ${searchText}`;

The query worked with DB Browser with SQLite but in the app didn't. You can test the queries here. The sample db file is here.

[DallasHoff]

REGEXP is a reserved word in SQLite's SQL syntax, but it is not defined in the SQLite engine by default. It is simply a hook to call a user-defined function. DB Browser for SQLite implements this function itself.

Using REGEXP with SQLocal should be possible once SQLocal supports defining custom scalar functions, which I've been planning to add (work in progress on branch "user-functions"). I'll work on it.

[DallasHoff]

Version 0.9.0 adds the createScalarFunction method, so you can now enable the REGEXP syntax with something like this:

await createScalarFunction('regexp', (pattern, value) => {
  const regexp = new RegExp(pattern);
  return regexp.test(String(value));
});