Regular expression queries using HSQLDB in Java

Question

I am trying to create a regular expression filter for my application. I use HSQLDB to store my messages and the regex.pattern [1] class to match incoming messages. I noticed that regex.pattern and LIKE in HSQLDB use different "teqniques".

Example

I want to combine: {"auth_user":"YQ==","auth_pass":"ZGFz"} .

With HSQLDB: SELECT * FROM messages LIKE %auth%

With regex.pattern: \bauth or auth

My questions

• Is there a way to get data from a user and query using RLIKE or REGEX in HSQLDB?

• Is there any easy way to convert regex.pattern into an HSQLDB query?

[1] https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html

Thanks in advance.

EDIT 1: The messages I receive are not only generated by JSON.

EDIT 2: I tried REGEXP_MATCHES as @JorgeCampos and @fredt, but I got the following SQL Error [S1000]: java.lang.ClassCastException: org.hsqldb.types.ClobDataID cannot be cast to java.lang.String exception SQL Error [S1000]: java.lang.ClassCastException: org.hsqldb.types.ClobDataID cannot be cast to java.lang.String when I execute the following SELECT * FROM WEBSOCKET_MESSAGE WHERE REGEXP_MATCHES(PAYLOAD_UTF8, '^a.*'); command SELECT * FROM WEBSOCKET_MESSAGE WHERE REGEXP_MATCHES(PAYLOAD_UTF8, '^a.*');

Answer 1

Use REGEXP_MATCHES(column_name, regular_expression)

The function uses the Java regular expression syntax.

If the column type is CLOB, use the cast command for VARCHAR

REGEXP_MATCHES(CAST (column_name AS LONGVARCHAR), regular_expression)