Search notes:

Oracle: Multilingual case insenstive sorting / string comparison

If sorting is set to multilingual case insenstive (nls_sort = ‥_m_ci or ‥_m_ai)
create table tq84_words (
    txt_1   nvarchar2(20),
    txt_2   nvarchar2(20)
);

begin
   insert into tq84_words values (n'foo!'  , n'_baz'  );
   insert into tq84_words values (n'___bar', n'bar!!!');
   insert into tq84_words values (n'?baz'  , n'.foo'  );
   commit;
end;
/


alter session set nls_sort = binary;
alter session set nls_comp = binary;

select * from tq84_words where txt_1 = txt_2;
select * from tq84_words order by txt_1;
select * from tq84_words order by txt_2;

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

select * from tq84_words where txt_1 = txt_2;
select * from tq84_words order by txt_1;
select * from tq84_words order by txt_2;

drop table tq84_words;

TODO

nls_sort=punctuation

Index