Search text in an oracle database for characters like 'a' to match the following characters: 'a', 'à', 'â' and 'ä'

I have an Oracle database with data in French language.

My users mentionned that they would like to be able to find data, regardless of accents on characters, so words like:

• École could be found by typing Ecole or École
• Père-Noël could be found by typing Pere-Noel or Père-Noel or Père-Noël

Since this is a search that will be made on many columns and on tables with many rows, that there are many characters which can have accents in French, I would like to have an efficient way to match accentuated characters with unnaccentuated version.

Does Oracle provide any 开发者_如何学Pythonfacility for this and if not, what solutions are available to do so ?

Check the documentation for Oracle fulltext indices (Oracle Text) - essentially, you'll want to create your own lexer for this.

To create a simple fulltext index:

create index fulltext_idx on tab(col) indextype is ctxsys.context; 

UPDATE: Using the auto_lexer might also be an option (since it supports French), see http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/cdatadic.htm#BHCGJHDH