Case and accent insensitive 'like' comparison oracle without altering session on oracle

Question

Hi all 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.

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.

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.

Thanks for your help!

Answer 1

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.

Answer 2

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:

upper(convert(col_name, 'US7ASCII')) like 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..

Thanks for your help

Answer 3

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