All geek questions in one place

Accidental and insensitive to accent "similar" comparative oracle without changing session on oracle

I need to be able to do “how” queries in several languages, so they umlate, and A is processed the same way and so on. I am on the 10gr2 oracle and I cannot change the session.

I tried things like

nls_upper(col_name,'NLS_SORT=BINARY_AI') like nls_upper('%fur%','NLS_SORT=BINARY_AI')

but I do not feel any joy. No matter what I do, the result of nls_upper seems to preserve umlauts on U, for example.

Is there any function or operator that I can use? Ideally, he also converts the German eszett - this funny character, similar to B - to double S.

Thank you for your help!

+3
source share
3 answers

Actually, it seems that the best solution is to convert both strings to US7ASCII, as this eliminates all the accents. Therefore, I can do:

upper (convert (col_name, 'US7ASCII')), e.g. upper (convert ('% okopla%', 'US7ASCII'))

The only wrinkle I discovered is that the German eszett is turning into ?. Therefore, I will just search in my search term, and if it contains funny B, then I am going to use Gary NLS_UPPER ('große', 'NLS_SORT = XGerman'), otherwise I will just convert to ASCII. These are a bit of shreds, but we only need to cover English, French and German, so I think everything will be fine.

thanks for the help

+2
source

Oracle, NLS_COMP = LINGUISTIC, NLS_SORT = XGERMAN_AI , Eszett. , Eszetts , replace().

+1

Is this consistent with 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 need to develop requirements a bit.

0
source

Source: https://habr.com/ru/post/1765012/

More articles:


All Articles