How to do a like case-insensitive and accent insensitive in Oracle 10gR2 and JPA?
Solution 1
Crudely, you can do something like
select upper(convert('This is a têst','US7ASCII')),
upper(convert('THIS is A test','US7ASCII'))
from dual;
select 1 from dual
where upper(convert('This is a têst','US7ASCII')) =
upper(convert('THIS is A test','US7ASCII'))
The CONVERT reduces the accented characters to the mapped ASCII equivalent, and the UPPER forces lower case to uppercase. The resultant strings should be matchable.
Solution 2
(...) using JPA, how can I force a like query to be case insensitive and accent insensitive?
My answer will be JPQL oriented. For the former part, you could do:
where lower(name) like 'johny%';
For the later part, I'm not aware of a standard JPQL way to do it.
At the end, altering the session variables NLS_COMP
and NLS_SORT
is IMO the best option.
AlfaTeK
Updated on July 19, 2022Comments
-
AlfaTeK almost 2 years
In a J2EE project, using JPA, how can I force a like query to be case insensitive and accent insensitive?
I know about changing session variables NLS_COMP and NLS_SORT but I'm wondering if there is another trick to do this in the query itself, without changing session variables
-
AlfaTeK over 13 yearsThat doesn't solve the accent insensitive problem. SELECT NLS_UPPER ('áçgroe', 'NLS_SORT = XWEST_EUROPEAN_AI') "Uppercase" FROM DUAL; // returns ÁÇGROE which still has accents
-
AlfaTeK over 13 yearsThis is not JPA compatible (no convert support) but it will work using hibernate/oracle so i'm accepting this answer... hope to not be unfair :)
-
Martin Schapendonk over 13 yearsI didn't look into all possible values of the NLS_SORT parameter. I guess there should be one that eliminates the accents. I'll see if I can add anything useful after my weekend.
-
Ben George almost 8 years'Unfair' or wrong ? =) You asked for answer using JPA and accepted one that is not JPA compatible.