不改变 oracle 会话的情况下不区分大小写和重音的“喜欢”比较 oracle
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3723382/
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
Case and accent insensitive 'like' comparison oracle without altering session on oracle
提问by bruce
I need to be able to do 'like' queries across several languages, so that a umlaut, a and A are treated the same and so on. I'm on 10gr2 of oracle and I can't alter the session.
我需要能够跨多种语言进行“喜欢”查询,以便对元音变音、a 和 A 进行相同处理,等等。我在 oracle 的 10gr2 上,我无法更改会话。
I've been trying things like
我一直在尝试这样的事情
nls_upper(col_name,'NLS_SORT=BINARY_AI') like nls_upper('%fur%','NLS_SORT=BINARY_AI')
but I'm not having any joy. Whatever I do, the result of nls_upper seems to preserve the umlauts on U for example.
但我没有任何快乐。无论我做什么,nls_upper 的结果似乎都保留了 U 上的元音变音。
Is there any function or operator I can use? Ideally, it would also convert the German eszett - that funny characted that looks like a B - into double S.
我可以使用任何函数或运算符吗?理想情况下,它还会将德国的 eszett - 看起来像 B 的有趣字符 - 转换为双 S。
Thanks for your help!
谢谢你的帮助!
回答by bruce
So actually it seems like the best solution is to convert both string to US7ASCII, as that strips out all accents. So I can do:
所以实际上似乎最好的解决方案是将两个字符串都转换为 US7ASCII,因为这样会去掉所有的重音符号。所以我可以这样做:
upper(convert(col_name, 'US7ASCII')) like upper(convert('%okopla%','US7ASCII'))
upper(convert(col_name, 'US7ASCII')) 像 upper(convert('%okopla%','US7ASCII'))
The only wrinkle I've found is that the German eszett gets converted to ?. So I'm just going to look in my search term and if it contains a funny B, then I'm going to use Gary's NLS_UPPER ('gro?e', 'NLS_SORT = XGerman'), otherwise I'll just do the conversion to ASCII. It's a little kludgy, but we only have to cover english, french and german, so I think it will be OK..
我发现的唯一皱纹是德国 eszett 被转换为 ?。所以我只需要查看我的搜索词,如果它包含一个有趣的 B,那么我将使用 Gary 的 NLS_UPPER ('gro?e', 'NLS_SORT = XGerman'),否则我就只做转换为 ASCII。这有点笨拙,但我们只需要涵盖英语,法语和德语,所以我认为它会好的..
Thanks for your help
谢谢你的帮助
回答by gpeche
According to Oracle documentation, you need to specify both NLS_COMP=LINGUISTIC and NLS_SORT=XGERMAN_AI to be both umlaut and Eszett insensitive. I guess you must replace Eszetts by hand, with a replace()
.
根据Oracle 文档,您需要将 NLS_COMP=LINGUISTIC 和 NLS_SORT=XGERMAN_AI 都指定为变音和 Eszett 不敏感。我想您必须手动替换 Eszetts,并使用replace()
.
回答by Gary Myers
Does this fit the bill ?
这符合要求吗?
select case when NLS_UPPER ('gro?e', 'NLS_SORT = XGerman')
like '%SS%' then 'YES' else 'no' end match
from dual;
If not, you have to elaborate the requirements a bit.
如果没有,您必须详细说明要求。