title: Oracle: Multi-Column "Closest Match" Autocomplete Strategy
body: <p>I'm implementing an autocomplete that will allow a user to enter in partial text that will then be matched against 4 different columns in a table.  Here is a basic example:</p>
  
  <pre><code>+------------+-----------+--------+-----------------+
  | first_name | last_name | login  | email           |
  +------------+-----------+--------+-----------------+
  | John       | Smith     | jsmith | jsmith@foo.bar  |
  | Johnny     | Ringo     | ringo  | ringer@hmm.okay |
  | Bob        | Jones     | bjones | j1234@xyz.abc   |
  | Jane       | Doe       | doej   | doedoe@blah.umm |
  +------------+-----------+--------+-----------------+
  </code></pre>
  
  <p>When the user enters "jo", I want to match the records from this table where at least one of those four columns matches the pattern "jo%".  For this example, only the first two would match due to their <code>first_name</code> column values.  If the search were "js", then only the first record would match due to its <code>login</code> and <code>email</code> column values.  And so on.  I'd also like to return the results ordered by similarity, where the first result is the "closest match", and so on down the results (standard autocomplete behavior).</p>
  
  <p>I've currently been trying to solve this problem using <a href="http://pbarut.blogspot.com/2006/12/utlmatch-string-similarity-in-oracle.html" rel="nofollow"><code>UTL_MATCH</code></a>, and code that produces the following query:</p>
  
  <pre><code>SELECT first_name,
    last_name,
    login,
    email,
        (  utl_match.jaro_winkler_similarity(first_name, 'js')
         + utl_match.jaro_winkler_similarity(last_name, 'js')
         + utl_match.jaro_winkler_similarity(login, 'js')
         + utl_match.jaro_winkler_similarity(email, 'js')) similarity
  FROM users
  WHERE LOWER(first_name) LIKE LOWER('js%')
  OR LOWER(last_name) LIKE LOWER('js%')
  OR LOWER(login) LIKE LOWER('js%')
  OR LOWER(email) LIKE LOWER('js%')
  ORDER BY similarity DESC
  </code></pre>
  
  <p>The results aren't as accurate as I'd like them to be, and I've seen autocompletes in the wild that work the way I'd like mine to work, but have no idea how they're implemented on the back-end.</p>
  
  <p>Can anyone point me in the right direction?</p>