Search notes:

Oracle: Influence of NLS_SORT and NLS_COMP to equality of text (strings)

The following SQL script tries to demonstrate how the values of NLS_LANG and NLS_COMP influence the (perceived) equality of text.
drop   table tq84_nls_sort_comp_test purge;
create table tq84_nls_sort_comp_test (
   text varchar2(10)
);

insert into tq84_nls_sort_comp_test values ('René');
insert into tq84_nls_sort_comp_test values ('Rene');
insert into tq84_nls_sort_comp_test values ('RENÉ');
insert into tq84_nls_sort_comp_test values ('RENE');

create or replace view tq84_nls_sort_comp_test_v as
select
   t1.text t1,
   t2.text t2
from
   tq84_nls_sort_comp_test t1 join
   tq84_nls_sort_comp_test t2 on t1.text = t2.text and
                                 t1.rowid <> t2.rowid;

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

select * from tq84_nls_sort_comp_test_v;
-- No records returned

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

select * from tq84_nls_sort_comp_test_v;
-- No records returned

----------------------------------------
alter session set nls_comp = linguistic;
alter session set nls_sort = latin;

select * from tq84_nls_sort_comp_test_v;
-- No records returned

---------------------------------------
alter session set nls_comp = linguistic;
alter session set nls_sort = latin_ci;

select * from tq84_nls_sort_comp_test_v;
--
-- RENÉ  René
-- RENE  Rene
-- René  RENÉ
-- Rene  RENE

---------------------------------------
alter session set nls_comp = linguistic;
alter session set nls_sort = latin_ai;

select * from tq84_nls_sort_comp_test_v;
--
-- Rene  René
-- RENÉ  René
-- RENE  René
-- René  Rene
-- RENÉ  Rene
-- RENE  Rene
-- René  RENÉ
-- Rene  RENÉ
-- RENE  RENÉ
-- René  RENE
-- Rene  RENE
-- RENÉ  RENE

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

select * from tq84_nls_sort_comp_test_v;
-- No records returned
Github repository Oracle-patterns, path: /init-parameters/nls/sort-comp/eq.sql

See also

varchar2

Index