If the string really has been serialized as a byte array into a BLOB
column, then I'm not aware of an off-the-shelf solution. I would tackle
this problem by writing a function which does the following:
o Retrieves the java.sql.Blob via PreparedStatement.getBlob()
o Retrieves an InputStream from the BLOB via Blob.getBinaryStream()
o Decodes the InputStream's bytes into characters using a
java.nio.charset.CharsetDecoder obtained from a java.nio.charset.Charset
o Examines the decoded character stream for the pattern you want.
Hope this helps,
-Rick
On 9/30/19 6:18 PM, Alex O'Ree wrote:
yup, definitely a blob. it looks like the like operator doesnt work for
blobs, or maybe i need a cast or some function to the conversion
On Mon, Sep 30, 2019 at 6:50 PM Rick Hillegas <rick.hille...@gmail.com>
wrote:
On 9/30/19 9:18 AM, Alex O'Ree wrote:
I have a use case where i have string data stored in a blob and i want to
perform a query similar to
select * from table where column1 like '%hello world%'
It doesn't look like this is possible with derby out of the box. Is there a
way to create a function that calls a java function or something that can
be used to make this work?
Just to clarify, the column has type BLOB rather than CLOB? The following
script works for me on CLOB types:
connect 'jdbc:derby:memory:db;create=true';
CREATE TABLE t (a CLOB);
INSERT INTO t VALUES ('this will not match'), ('but shello worldly will');
SELECT * FROM t WHERE a LIKE '%hello world%';
Thanks,
-Rick
