Database Software

Hello,

General question : What is the correct way to do accent insensitive searches using the LIKE operator?

More specifically with the following settings:

alter session set nls_sort=punctuation; -- At a client's site, this can be swedish, german, arabic, etc.
alter session set nls_comp=linguistic;

And data:

create table t(a varchar2(50));
insert into t values ('Dépôt assignment with accents');
insert into t values ('depot assignment without accents');
insert into t values ('Somehting else');
commit;

I would like to do an accent-insensitive search such as:

select * from t where a like 'depot assignment %';

Obviously, that doesn't work. So I tried using nlssort and got mixed results:

-- Tried using punctuation (to match my NLS_SORT session setting) but it does not work
select a from t where nlssort(a, 'nls_sort=punctuation_ai') like nlssort('depot AssigNment ', 'nls_sort=punctuation_ai')||'%';

-- Seems to work but I don't know if that is the right way to do it
select a from t where nlssort(a, 'nls_sort=generic_m_ai') like nlssort('depot AssigNment ', 'nls_sort=generic_m_ai')||'%';

-- Does not work
select a from t where nlssort(a, 'nls_sort=french_ai') like nlssort('depot AssigNment ', 'nls_sort=french_ai')||'%';

-- Seems to work but I don't know if that is the right way to do it. Why do I have to use _M (multilingual) when I use FRENCH?
select a from t where nlssort(a, 'nls_sort=french_m_ai') like nlssort('depot AssigNment ', 'nls_sort=french_m_ai')||'%';

One last test I did by setting the sessions's NLS_SORT to binary_ai also seemed to work :

alter session set nls_sort=binary_ai;
alter session set nls_comp=linguistic;

-- Did not have to use nlssort function here:
select distinct a from t where a like 'depôt àssignment %';

I'm testing on Oracle Database 11gR1.

All helpful comments would be appreciated. If there is any other information you need, please let me know.

Thanks,

Mark T.

Edited by: Mark_T on Jun 5, 2012 2:46 PM