5

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.*');

Manos Kirtas
  • 133
  • 1
  • 9
  • 2
    If you need to search JSON, then why not read that JSON into Java and use a parser (e.g. GSON) to examine its content? Using a regex here seems like inviting problems later on. – Tim Biegeleisen Mar 26 '18 at 13:07
  • 1 - Yes: https://www.tutorialspoint.com/hsqldb/hsqldb_regular_expressions.htm ; 2- No, AFAIK – Jorge Campos Mar 26 '18 at 13:08
  • If you are manipulating JSON a lot inside your SQL statements, you might want to consider switching to a DBMS that directly supports JSON and operations on JSON documents. E.g. [PostgreSQL](https://www.postgresql.org/docs/current/static/functions-json.html) has very good support for JSON –  Mar 26 '18 at 13:13
  • @TimBiegeleisen I like your approach but the messages I get there are not only JSONs. – Manos Kirtas Mar 26 '18 at 13:26
  • @JorgeCampos That's strange but doesn't work for me. I use DBeaver Universal SQL to execute the command but I get Exceptions `java.sql.SQLException: java.lang.ClassCastException: org.hsqldb.types.ClobDataID cannot be cast to java.lang.String .` Same as in Java app. – Manos Kirtas Mar 26 '18 at 14:44
  • 1
    Just reading the error I know that you have a CLOB data type in your database which you are using the regex functions with. I take that since clob data is stored as stream it doesn't support regex (although I didn't find any explicit mention to that in the docs) – Jorge Campos Mar 26 '18 at 14:55

1 Answers1

1

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)

fredt
  • 24,044
  • 3
  • 40
  • 61