title: SQL Fuzzy Matching
body: <p>Hope i am not repeating this question. I did some search here and google before posting here.</p>
  
  <p>I am running a eStore with SQL Server 2008R2 with Full Text enabled.</p>
  
  <h1>My requirements,</h1>
  
  <ol>
  <li>There is a Product Table, which has product name, OEM Codes, Model which this product fits into. All are in text. </li>
  <li>I have created a new column called TextSearch. This has concatenated values of Product Name, OEM Code and Model which this product fits in. These values are comma separated.</li>
  <li>When a customer enters a keyword, we run search on TextSearch column to match for products. See matching logic below.</li>
  </ol>
  
  <p>I am using a Hybrid Fulltext and normal like to do search. This gives more relevant results. All the queries executed in to a temp table and distincts were returned.</p>
  
  <p>Matching logic,</p>
  
  <ol>
  <li><p>Run following SQL to get relevant product using full text. But @Keywords will be pre-processed. Say 'CLC 2200' will be changed to 'CLC* AND 2200*'</p>
  
  <p>SELECT Id FROM dbo.Product WHERE CONTAINS (TextSearch ,@Keywords)</p></li>
  <li><p>Another query will be running using normal like. So 'CLC 2200' will be pre-processed to 'TextSearch like %clc% AND TextSearch like %2200%'. This is simply because full text search wont search patterns before the keywords. example, it wont return 'pclc 2200'.</p>
  
  <p>SELECT Id FROM dbo.Product WHERE TextSearch  like '%clc%' AND TextSearch like '%2200%'</p></li>
  <li><p>If step 1 and 2 didn't return any records, following search will be executed. Value 135 was fine tuned by me to return more relevant records.</p>
  
  <p>SELECT p.id FROM dbo.Product AS p INNER JOIN FREETEXTTABLE(product,TextSearch,@Keywords) AS r ON p.Id = r.[KEY] WHERE r.RANK > 135</p></li>
  </ol>
  
  <p>All of above combined works fine in a reasonable speed and returns relevant products for keywords.</p>
  
  <p>But i am looking for to further improve when there is no product found at all.</p>
  
  <p>Say if customer looks for 'CLC 2200npk' and this product wasn't there, i needed to show next very close by 'CLC 2200'.</p>
  
  <p>So far i tried using <strong>Soundex()</strong> function. Buy computing soundex value for each word in TextSearch column and comparing with soudex value of keyword. But this returns way too many records and slow too.</p>
  
  <p>example, 'CLC 2200npk' will return products such as 'CLC 1100' etc. But this wouldn't be a good result. As it is not close to CLC 2200npk</p>
  
  <p>There is another good one <a href="https://stackoverflow.com/questions/921978/fuzzy-matching-using-t-sql">here</a>. but this uses CLR Functions. But i can not install CLR functions on the server.</p>
  
  <p>So my logic would need to be,</p>
  
  <p>if 'CLC 2200npk' not found, show close by 'CLC 2200'
  if 'CLC 2200' not found, show next close by 'CLC 1100'</p>
  
  <h1>Questions</h1>
  
  <ol>
  <li>Is it possible to match like as suggested?</li>
  <li>If i would need to do spelling correction and search, what would be good way? All of our product listing is in English.</li>
  <li>Is there any UDF or SP's to match texts like my suggestions?</li>
  </ol>
  
  <p>Thanks.</p>