Re: Character translation in index?

Hallvard,

Experiment with the SOUNDEX SQL function to see how well it works for your problem. It uses an algorithm that drops all vowels and follows other rules to compare words that are spelled differently, but sound alike in English. I had to resort to creating a "soundex last name" column in a person table and indexing it for searches on English spellings of Arabic names. It worked quite well, even finding all 7 English spellings of Muhammed. It is also great for American - British translation.

Syntax is like:

	select ename from emp
	where soundex(ename) = soundex('SMYTHE');

Hope this helps,

Bob Born (bjorn, borne, bourn, bourne, bern)

Hallvard B Furuseth wrote:
>
> Hi,
>
> I want to make case-insensitive and preferbly "accent-insensitive"
> searches. I.e. a user who searches for "HELENE" should find "Helene"
> and preferably also "H<e'>l<e`>ne", where <e'> and <e`> are "e" with
> grave and acute. (if these are available in the charset, of course.)
>
> I note that this can be done with SELECT, but then I suppose an index
> can't be used so the entire table must be searched. Or can the index
> be built _with_ character translation? Or maybe the datatype could
> somehow be defined so that all compares and indexing will be done with
> a specified character translation?
>
> Note that I know some SQL but not Oracle, it won't be my job to
> implement this. I've just browsed the Oracle manuals a bit. Though
> of course I can always ask a local Oracle guru to translate any
> replies if they get too cryptic:-)
>
> --
> Regards,
>
> Hallvard
Received on Thu Feb 15 1996 - 00:00:00 CET