Accent-Insensitive search and LIKE
Mark_TJun 5 2012 — edited Jun 10 2012Hello,
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