Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to do a like case-insensitive and accent insensitive in Oracle 10gR2 and JPA?

Tags:

java

oracle

jpa

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

like image 466
AlfaTeK Avatar asked Nov 18 '10 19:11

AlfaTeK


2 Answers

(...) 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.

like image 135
Pascal Thivent Avatar answered Nov 19 '22 09:11

Pascal Thivent


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.

like image 22
Gary Myers Avatar answered Nov 19 '22 09:11

Gary Myers