javaregexdatabasehsqldbrlike

Regular Expresions queries with HSQLDB in Java

I am trying to create a regex filter for my app. I am using HSQLDB to store my messages and regex.pattern[1] class to match the incoming messages. I noticed that regex.pattern and LIKE in HSQLDB uses diferent matching "teqniques".

Example

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

With HSQLDB: SELECT * FROM messages LIKE %auth%

With regex.pattern: \bauth or auth

My Questions

  1. Is there any way to get the input from user and query with RLIKE or REGEX in HSQLDB?

  2. Is there any easily way to convert regex.pattern in HSQLDB query?

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

Thank you in advance.

EDIT 1: The messages I get are not only JSON formated.

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

Solution

  • Use REGEXP_MATCHES(column_name, regular_expression)

    The function uses Java regular expression syntax.

    If the column type is CLOB, use a cast to VARCHAR

    REGEXP_MATCHES(CAST (column_name AS LONGVARCHAR), regular_expression)