JPA Criteria Expression Replace Character

Question

In type String to replace all - with * I can write

String firstName = firstName.replaceAll("-", "*");

Is there a way to do the same in Criteria Expression?

I need to remove special characters from the first name before comparing it to the pattern in like method.

My expression:

Expression<String> firstName = root.get("firstName");
builder.like(firstName, pattern);

I don't understand what you are trying to achieve. Can you explain why you want to change the column name? — Simon Martinelli, Aug 03 '18 at 14:00
@SimonMartinelli I don't want to change the column name. I just want to remove any special character in the returned firstName — Jumana Alhaddad, Aug 03 '18 at 14:14
I see but then you have to do that in the result. The Expression is for building the query.. What's the result of you query' — Simon Martinelli, Aug 03 '18 at 15:54
@SimonMartinelli I have updated the post. I need to replace the characters before I run the query. — Jumana Alhaddad, Aug 03 '18 at 16:11
where do you want to replace the characters? In the column in the database? — Simon Martinelli, Aug 03 '18 at 16:22
@SimonMartinelli just in the search. For example, if my last name in the database is `Al-haddad` I want to remove the `-` in my query just for the comparing, I don't want to really change my last name value in the database itself. — Jumana Alhaddad, Aug 03 '18 at 16:39
the you must use a function on the field: Have a look here http://openjpa.208410.n2.nabble.com/query-that-uses-String-REPLACE-function-td6344747.html — Simon Martinelli, Aug 04 '18 at 10:11

score 0 · Accepted Answer · answered Aug 06 '18 at 00:49

0

Thanks to @SimonMartinelli comment. My issue is solved by using

builder.function("REGEXP_REPLACE", String.class, firstName, builder.literal("[^a-zA-Z0-9]+"), builder.literal(""));

answered Aug 06 '18 at 00:49

Jumana Alhaddad

1 Answers1