1

I am using H2 in-memory DB. I have a DB column that stores data as a CLOB data type. I know how to store a CLOB and read it from DB.
The issue is I am looking to have a wildcard search using the LIKE clause in SQL query.

My column name is 'PAYLOAD'

JDBC query I tried,

SELECT * FROM TABLE WHERE PAYLOAD LIKE '%SOME-STRING%';
SELECT * FROM TABLE WHERE TO_CHAR(PAYLOAD) LIKE '%SOME-STRING%';

These queries run fine with oracle but don't return anything when spring JDBC connects to H2 DB and also no exception etc. I tried with other varchar columns and that gives me results with like clause(sanity check). What configuration do I need to have here?

bhagya
  • 41
  • 4
  • 1
    Post your current java based query code? – Atmas Mar 13 '21 at 22:12
  • It's really nothing, a simple jdbc template making a query mentioned above. List res=template.query("select * from table where payload like ?", "%"+ keywordtosearch+"%"); could not post exact as commented from mobile – bhagya Mar 13 '21 at 23:07
  • Edited my question - its specific to in memory H2 DB connection. – bhagya Mar 14 '21 at 13:32

1 Answers1

0

Thanks for responding to comment.

Based on that, you could try to move the "%" part of your prepared statement value into the query. It's may be getting escaped (i.e. interpretted literally).

Looking again, I see a good SO thread on this which sort of advises differently so take a look here too:

Using "like" wildcard in prepared statement

Atmas
  • 2,389
  • 5
  • 13
  • Thanks for your reply. I can get results for other varchar column based on wildcard and like Clause. Only when I try searching clob column, it gives 0 rows. – bhagya Mar 14 '21 at 08:30