title: Google Style Search Suggestions with Levenshtein Edit Distance
body: <p>Ok guys working on search suggestions using jQuery-UI AutoComplete with results from sql-sever 2008 db. Using AdventureWorks DB Products table for testing. I want to search across 2 fields in this example. ProductNumber and Name.</p>
  
  <p>I asked 2 questions earlier relating to this...<a href="https://stackoverflow.com/questions/4518927/t-sql-stored-procedure-to-return-google-style-suggested-search-results">here</a> and <a href="https://stackoverflow.com/questions/4519281/most-efficent-way-to-limit-rows-returns-from-union-query-tsql">here</a></p>
  
  <p>and ive come up with this so far...</p>
  
  <pre><code>CREATE procedure [dbo].[procProductAutoComplete]
  (
      @searchString nvarchar(100)
  )
  as
  begin
  
      declare @param nvarchar(100);
      set @param = LOWER(@searchString);
  
  WITH Results(result)
  AS
  (
      select TOP 10 Name as 'result'
      from Production.Product 
      where LOWER(Name) like '%' + @param + '%' or (0 &lt;= dbo.lvn(@param, LOWER   (Name), 6))
      union
      select TOP 10 ProductNumber as 'result'
      from Production.Product
      where LOWER(ProductNumber) like '%' + @param + '%' or (0 &lt;= dbo.lvn(@param,  LOWER(ProductNumber), 6))
  )
  
  SELECT TOP 20 * from Results
  
  end;
  </code></pre>
  
  <p>My problem now is ordering of the results...I am getting the correct results but they are just ordered by the Name or product number and are not relevant to the input string...</p>
  
  <p>for example I can search for product Number starting with "BZ-" and the top returned results are ProductNums starting with "A" although I do get more relevant results elsewhere in the list..</p>
  
  <p>any ideas for sorting the results in terms of relevance to the search string??</p>
  
  <p>EDIT:</p>
  
  <p>in regards to the tql implementation of the levenschtein distance found <a href="https://stackoverflow.com/questions/560709/levenshtein-distance-in-t-sql">here</a>(linked to in previous question)...</p>
  
  <p>I am wondering what would be the best way to determine the MAX value to send to the function (6 in my example above)</p>
  
  <p>Would it be best to choose an arbitrary value based on what "seems" to work well for my given data set? or would it be best to adjust it dynamically based on the length of the input string...</p>
  
  <p>My initial thoughs were that the value to should be inverely proportional to the length of the searchString...so as the search string grows and becomes more specific..the tolerance decreases...thoughts??</p>