Path: news.f.de.plusline.net!news-fra1.dfn.de!news.visyn.net!news2.arglkargh.de!news.osn.de!diablo1.news.osn.de!newsfeed.freenet.de!213.132.189.2.MISMATCH!multikabel.net!feed20.multikabel.net!news2.euro.net!postnews2.euro.net!news.wanadoo.nl!not-for-mail
From: "R. Schierbeek" <byteNospam@gmail.com>
Newsgroups: comp.databases.oracle.server
References: <1170498128.435126.78800@a75g2000cwd.googlegroups.com>
Subject: Re: How to perform fuzzy search on varchar columns?
Date: Sat, 3 Feb 2007 14:22:13 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-Antivirus: avast! (VPS 0709-2, 01-02-2007), Outbound message
X-Antivirus-Status: Clean
Lines: 32
Message-ID: <45c48bb6$0$89244$dbd4f001@news.wanadoo.nl>
Organization: Wanadoo
NNTP-Posting-Date: 03 Feb 2007 13:18:46 GMT
NNTP-Posting-Host: c529c23b9.cable.wanadoo.nl
X-Trace: 1170508726 dr4.euro.net 89244 82.156.35.185:3151
X-Complaints-To: abuse@wanadoo.nl
Xref: news.f.de.plusline.net comp.databases.oracle.server:193543

 <frank_ratzlow@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@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