Re: How to perform fuzzy search on varchar columns?

 <frank_ratzlow_at_hotmail.com> schreef
> Hi folks,
> I would like to perform queries on a varchar column that finds not
> only exactly spelled words but also words that sound similar and
> tolerates minor typos.
> E.g. I have a column "surname" and search for "Meyer" I also want to
> have returned "Meier", "Maier" and so on.
> I have read a bit about Oracle Text, but this seems to big for what we
> require. Especially we don't need searching within uploaded files!
> Could anyone be so kind to give me a hint, whether we have to install
> an additional package or if Oracle delivers it out of the box. What
> would a sample query look like?
> TIA
>
> Frank
> frank_ratzlow_at_nojunk.hotmail.com
> P.S. Oracle 9iR2

hello Frank,
You can use Metaphone instead of Soundex, an algorithm for encoding a word so that similar sounding words encode the same. It's similar to soundex in purpose, but as it knows the basic rules of English pronunciation it's much more accurate. Soundex is part of Oracle; Metaphone is not (yet). The higher accuracyof Metaphone doesn't come free, though, metaphone requires more computational power as well as more storage capacity.
There are a few versions of Metaphone available for PL/sql; i like the one made by Scott Stephens the best.

Cheers,

   Roelof Schierbeek; DBA Received on Sat Feb 03 2007 - 07:22:13 CST