如何在 Oracle 10gR2 和 JPA 中做不区分大小写和不区分重音的类似操作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4218780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do a like case-insensitive and accent insensitive in Oracle 10gR2 and JPA?
提问by AlfaTeK
In a J2EE project, using JPA, how can I force a like query to be case insensitive and accent insensitive?
在 J2EE 项目中，使用 JPA，如何强制类似查询不区分大小写和重音不敏感？
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
我知道更改会话变量 NLS_COMP 和 NLS_SORT 但我想知道是否有另一个技巧可以在查询本身中执行此操作，而无需更改会话变量
采纳答案by Gary Myers
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.
CONVERT 将重音字符减少为映射的 ASCII 等效字符，而 UPPER 强制小写为大写。结果字符串应该是可匹配的。
回答by Pascal Thivent
(...) using JPA, how can I force a like query to be case insensitive and accent insensitive?
(...) 使用 JPA，如何强制类似查询不区分大小写和重音不敏感？
My answer will be JPQL oriented. For the former part, you could do:
我的答案将是面向 JPQL 的。对于前一部分，您可以执行以下操作：
where lower(name) like 'johny%';
For the later part, I'm not aware of a standard JPQL way to do it.
对于后面的部分，我不知道标准的 JPQL 方法。
At the end, altering the session variables NLS_COMPand NLS_SORTis IMO the best option.
最后，改变会话变量NLS_COMP，并NLS_SORT为IMO的最佳选择。
回答by Martin Schapendonk
You could use NLS_UPPER for that without altering the session:
您可以在不更改会话的情况下使用 NLS_UPPER：
select 1
from dual
where nls_upper('gro?e', 'NLS_SORT = XGerman') like '%OSSE%';
