Ignore accents in Oracle queries

by Granville Bonyata on October 30, 2014

--Set the comparison to LINGUISTIC
alter session set NLS_COMP=LINGUISTIC;

--Set the SORT to GENERIC_M_AI (AI means Accent insensitive, and case insensitive)
alter session set NLS_SORT=GENERIC_M_AI;


--Create a table and load some data
CREATE TABLE test_query (my_text  VARCHAR2(100))
/

insert into test_query (my_text) VALUES ('test-');
insert into test_query (my_text) VALUES ('TEST-');
insert into test_query (my_text) VALUES ('TÉST-');
insert into test_query (my_text) VALUES ('tést-');
insert into test_query (my_text) VALUES ('test');

--Create a function based index on the accent insensitive values
CREATE INDEX test_index ON test_query (NLSSORT(my_text, 'NLS_SORT=GENERIC_M_AI'))
/

--And now see that the diacritics are ignored. Note that the accent is ignored,
--it is case insensitive, and the punctuation is ignored

SELECT *
  FROM test_query
 WHERE my_text = 'test'
/

More at Oracle’s docs Linguistic Sorting and String Searching.

Previous post:

Next post: