SQL LIKE operator for Graphic Strings

Question

How can I use LIKE operator for the Graphic strings in OpenJPA for DB2?

I used as the following in the JPQL and failed.

select u from User u where u.userName like :userName

userName = VARGRAPHIC(20)

Then I use as the following and failed

   select u from User u where u.userName like CAST(:userName AS VARGRAPHIC(20))

Then I use as the following and failed

select u from User u where LOCATE(:userName,u.userName) > 0 

I tried switching from deferPrepare to true to false but still failed

edited

with LOCATE

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=LOCATE;FUNCTION, DRIVER=3.63.75

with LIKE

  DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=LIKE;FUNCTION, DRIVER=3.63.75 

Any kind help is appreciated.

edited

I concat the parameter with %%. The problem is seemed to be LIKE doesn't accept in-balanced operands. Both must be a graphic string. So I set So I set

RequiresCastForComparisons=true

, then the query result containing an escape(Which is using LIKE ESCAPE '\'). So I set

RequiresSearchStringEscapeForLike=false

.

After those setting. When I was searching the string 花火大会 the with wild card paramter eg. %花% return empty records.

I will update the question with generated SQL tomorrow.

Thanks in advance

Answer 1

I had to use the wildcard character as doublebyte "％"　instead of normal "%" Hope that this will help someone.