How to enable AI(accent insensitiv) for one column without changing session

Following is my test SQL:
create table accent_test(col varchar2(10 char);
insert into accent_test values('INDIA');
insert into accent_test values('india');
insert into accent_test values('InDiÄ ');

Following SQL can only enable CI(Case insensitive), but can’t enable AI(accent insensitive):
select * 
from accent_test 
where NLS_UPPER(col, 'NLS_SORT=GENERIC_BASELETTER') LIKE NLS_UPPER('INDIA', 'NLS_SORT=GENERIC_BASELETTER')
COL
INDIA
india
Upper SQL can’t return the third row ‘InDiÄ’ which includes accent character.

I know following SQL can enable AI by changing the session. However it will change the whole session and other columns in one table:
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; 
SQL> ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

 

Following lists two solutions I can find out to enable CIAI without changing the session. However, both of these two solutions have limitations:
1. where upper(convert(<CIAI_column>, 'JA16SJIS')) like upper(convert(<search_string>, 'JA16SJIS'))
Because the DB's character set is AL32UTF8, I didn't know whether convert(<CIAI_column>, 'JA16SJIS') may return messy code '?' for the characters which are not from character set JA16SJIS. 
 
2. use Equivalence Class '[= =]' in Regular Expressions. A base letter and all of the accented versions of the base constitute an equivalence class. For example, the equivalence class [=a=] matches ä as well as â.
Example SQL: 'SELECT col FROM accent_test WHERE REGEXP_LIKE(col, '[[=I=][=N=][=D=][=I=][=A=]]*','i');'
However, may cause performance issue. Even a index on accent_test(col) is created, we can't avoid full table scan.

转载本站文章请注明作者和出处:http://blog.youkuaiyun.com/crazy_samba ，请勿用于任何商业用途