search with national / multilingual chars with java and oracle

Question

Java, hibernate + oracle. Users are stored in datababase and have national characters in their names (ü, ß etc). Now I need to create a convenient search function. Example: when user type 'do', 'dö' or 'doe' then entry 'Fidörner' should be found. Currently there are only 3 rules like above one defined by business but I expect there will more.

what are the recommended solutions? google search appliance? lucene? hibernate search? solr? custom text normalization (can it be done quickly)? any other tools?

Answer 1

Take a look at Oracle Text. It's a free option installed by default on your database.

For example:

create table users(name varchar2(100));
insert into users values ('Fidörner');
insert into users values ('Fido');
insert into users values ('Smith');
commit;

begin
  ctx_ddl.drop_preference('mylex');
  ctx_ddl.create_preference('mylex', 'basic_lexer');
  ctx_ddl.set_attribute('mylex', 'base_letter', 'YES');
  ctx_ddl.set_attribute('mylex','alternate_spelling','german');
end;
/

create index users_index on users(name)
indextype is ctxsys.ctxcat  parameters ('LEXER mylex');

select * from users where catsearch(name, '**do*', null) > 0;
select * from users where catsearch(name, '**dö*', null) > 0;
select * from users where catsearch(name, '**doe*', null) > 0;

All return:

Name
----
Fidörner
Fido