title: Oracle Fuzzy text search
body: <p>How would one go about doing a fuzzy name search in Oracle?</p>
  
  <p>For example: </p>
  
  <p>Our data system has the preferred mailing as:</p>
  
  <p><strong>Mr. Nicolas Jim Raleigh</strong></p>
  
  <p>But in Facebook, or other search field, the name passed to the algorithm is:</p>
  
  <p><strong>Nick Jim Raleigh</strong></p>
  
  <p>The process would run the search name against all of the preferred names, then return the result that contains the most matching characters:</p>
  
  <p>Mr. [<strong>Nic</strong>]olas <strong>Jim Raleigh</strong></p>
  
  <p>[<strong>Nic</strong>]k <strong>Jim Raleigh</strong></p>
  
  <p>16 out of my searched name's 17 characters appear in the preferred name, and we could return a ranked suggestion.</p>
  
  <p>[EDITED TO ADD]</p>
  
  <p>After initial suggestion, and reading of <a href="http://docs.oracle.com/cd/B28359_01/text.111/b28303/query.htm" rel="nofollow">Oracle's Text Query options</a> I have created an index on the table </p>
  
  <pre><code>create index ADD_EX_INDX3 on address_extract(pref_mail_name) 
    indextype is ctxsys.context 
    parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');
  </code></pre>
  
  <p>and now am able sucessfully retireve</p>
  
  <pre><code>select score(1), ae.pref_mail_name
  from address_extract ae
   where contains(pref_mail_name,'fuzzy(raleigh,,,weight)',1) &gt; 0
  order by score(1) desck
  </code></pre>
  
  <p>Which returns </p>
  
  <pre><code>100 Mr. Raleigh H. Jameson
  100 Mr. Nicolas Jim Raleigh
  100 Ms. Susanne M. Raleigh
  66  Mrs. LaReign Smith
  66  Ms. Rahil Smith
  62  Mr. Smith  Ragalie
  </code></pre>
  
  <p>I am struggling to to a full name search however.  How would I go about doing the full name?</p>