Accent and case insensitive collation in Oracle with LIKE?

One method would be to modify your session parameters NLS_SORT and NLS_COMP : SQL> SELECT Name FROM CollationTestTable WHERE NAME LIKE '%pe%'; NAME -------------------------------------------------------------------------------- pepe SQL> alter session set nls_sort=Latin_AI; Session altered SQL> alter session set nls_comp=linguistic; Session altered SQL> SELECT Name FROM CollationTestTable WHERE NAME LIKE '%pe%'; NAME -------------------------------------------------------------------------------- pepe pépé PEPE As shown in another SO you cannot use the LIKE operator with NLSSORT (this is because NLSSORT returns a string of bytes that will be used for sorting, and LIKE only works with charater strings) Update: While setting the NLS parameters would be my first choice, you could also use built-in functions to achieve the same result. A couple of examples: SQL> SELECT Name 2 FROM CollationTestTable 3 WHERE upper(convert(NAME, 'US7ASCII')) 4 LIKE upper(convert('%pe%', 'US7ASCII')); NAME -------------------------------------------------------------------------------- pepe pépé PEPE SQL> SELECT Name 2 FROM CollationTestTable 3 WHERE upper(translate(NAME, 'à âéèêìîòôùûÿ', 'aaeeeiioouuy')) 4 LIKE upper(translate('%pe%', 'à âéèêìîòôùûÿ', 'aaeeeiioouuy')); NAME ----------------------------------- pepe pépé PEPE.

One method would be to modify your session parameters NLS_SORT and NLS_COMP: SQL> SELECT Name FROM CollationTestTable WHERE NAME LIKE '%pe%'; NAME -------------------------------------------------------------------------------- pepe SQL> alter session set nls_sort=Latin_AI; Session altered SQL> alter session set nls_comp=linguistic; Session altered SQL> SELECT Name FROM CollationTestTable WHERE NAME LIKE '%pe%'; NAME -------------------------------------------------------------------------------- pepe pépé PEPE As shown in another SO, you cannot use the LIKE operator with NLSSORT (this is because, NLSSORT returns a string of bytes that will be used for sorting, and LIKE only works with charater strings) Update: While setting the NLS parameters would be my first choice, you could also use built-in functions to achieve the same result. A couple of examples: SQL> SELECT Name 2 FROM CollationTestTable 3 WHERE upper(convert(NAME, 'US7ASCII')) 4 LIKE upper(convert('%pe%', 'US7ASCII')); NAME -------------------------------------------------------------------------------- pepe pépé PEPE SQL> SELECT Name 2 FROM CollationTestTable 3 WHERE upper(translate(NAME, 'à âéèêìîòôùûÿ', 'aaeeeiioouuy')) 4 LIKE upper(translate('%pe%', 'à âéèêìîòôùûÿ', 'aaeeeiioouuy')); NAME ----------------------------------- pepe pépé PEPE.

Other queries in the same session may require the original NLS settings. Cheers. – Ed .

Oct 15 '09 at 14:57 @Ed: you could save the session parameters before running the query (select * from nls_session_parameters) then putting them back after the query. – Vincent Malgrat Oct 15 '09 at 15:04 I have been testing this and all looked good but am now told that NLS_SORT and NLS_COMP solution is only supported in version 10 r2. I need to support Oracle databases with min version 9.

Is the only way I can perform the case and accent insensitive search is using functions? – Ed . Nov 2 '09 at 8:17 @Ed: NLS_SORT=linguistic seems to be a 10g feature.

You can still use the second method (SQL functions) in 9i. – Vincent Malgrat Nov 2 '09 at 8:58.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.