No such function: REGEXP

[RAnders00]

The SQLite grammar specification defines that there is support for usage of Regular expressions in any expr statement:

The driver does not support this function:

A sample query:

SELECT * FROM SAMPLE WHERE row REGEXP 'ab?c*';

This is a sample stacktrace, when preparing a statement:

[SQLITE_ERROR] SQL error or missing database (no such function: REGEXP)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: REGEXP)
    at org.sqlite.core.DB.newSQLException(DB.java:890)
    at org.sqlite.core.DB.newSQLException(DB.java:901)
    at org.sqlite.core.DB.throwex(DB.java:868)
    at org.sqlite.core.NativeDB.prepare(Native Method)
    at org.sqlite.core.DB.prepare(DB.java:211)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:40)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:29)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:18)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:47)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:254)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:226)

The stacktrace is cut, all classes below the snippet are my classes.

I am using org.xerial:sqlite-jdbc:3.8.11.2 straight from Maven Central.

[everbeek]

The REGEXP is used to access a user defined function. I got some code from a stackoverflow answer, as below. I just found this minutes ago, and haven't yet used the regex search (middle of refactoring), but it did get rid of the exception.

http://stackoverflow.com/questions/25786996/sqlite-query-with-regex

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);
                    }
                });

[ghost]

It would be nice for this repo to publish Javadocs on github pages or wherever. E.g. its not obvious what the package for Function class is. I had to google to find this out but I believe it can be better :)

FYI http://javadox.com/org.xerial/sqlite-jdbc/3.8.11.2/org/sqlite/package-summary.html

[xerial]

It seems regexp is not available even in the latest command line:

$ sqlite3
SQLite version 3.16.1 2017-01-03 18:27:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> with t as (values ('hello')) select * from t where t regexp 'hell*';
Error: no such function: regexp

If sqlite-jdbc supports this, @everbeeks's approach is better, but this will be Java-style REGEXP syntax, and can be different behavior with the other sqlite distributions.

[ceinmart]

Hi,
Please, can anyone explain to me if this resource should be work with sqlite-jdbc or not?
I'm trying to use it with 3.32.3.1, without success.

[xerial]

SQLite itself doesn't provide any regexp implementation, so we need to register a UDF like #60 (comment)

[ceinmart]

The REGEXP is used to access a user defined function. I got some code from a stackoverflow answer, as below. I just found this minutes ago, and haven't yet used the regex search (middle of refactoring), but it did get rid of the exception.

http://stackoverflow.com/questions/25786996/sqlite-query-with-regex

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);
                    }
                });

Hi , Trying to run this UDR , I got the error

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (near "Function": syntax error)

What is wrong? Any tips?
Using last jdbc available: 3.32.3.2

[ceinmart]

SQLite itself doesn't provide any regexp implementation, so we need to register a UDF like #60 (comment)

@xerial , please, how to register that?
I'm looking at the SQLite documentation and as far I understand, this should be implemented at java compilation, DBeaver level.
So, as user I can't implement that during my connection. Is right?
Or I should create a .java file with this function, compile it and add to load with the SQLite driver?

[AhmedIbrahim12]

SQLite itself doesn't provide any regexp implementation, so we need to register a UDF like #60 (comment)

@xerial , please, how to register that? I'm looking at the SQLite documentation and as far I understand, this should be implemented at java compilation, DBeaver level. So, as user I can't implement that during my connection. Is right? Or I should create a .java file with this function, compile it and add to load with the SQLite driver?

@ceinmart i've currently managed to configure it through a CustomDataSource and then using it as you like whenever needed. hope this helps

[hrieke]

So I'll chime in here with Good New / Bad News concerning the solution:
Good News, it works. I ran the solution in some code against a test database of 86MB and the same query in DB Browser for SQLite; both returned the same results.

Bad News, is that the solution is from Stack OverFlow, so it is covered by the Creative Commons Share Alike 4.0 License. If your code is going to be commercial in nature, I would strongly advise speaking to legal first.

So... To the developers of sqlite-jdbc, please can you bake a solution into the driver?