Fuzzy Search with SQL?

[jwelch]

Using SQL with a ODBC source...<br>
<br>
Is there a way that I can create a query that will return a word approximate score such as: (refering to a table with first_name (text), last_name (text), ...<br>
<br>
A user could enter &quot;Colinsworth&quot; as a parameter and the query would return all records that had an exact match of Colinsworth (which would be 0 records in this case with a score of 100%). <br>
<br>
Also, it would return the records of &quot;Collinsworth&quot; at a very high score and records of &quot;Collins&quot; at a medium score.<br>
<br>
I understand how to set up SQL queries using parameters, I just don't know how to score word approximatly.<br>
<br>
The underlying result would be a name search that would return the closest name(s) if an exact name isn't found<br>
<br>
Thanks

 

[Guest_imported]

To use this solution you must :<br>1) Use SQL-Server 7<br>2) Enable Full Text Indexing (must run Standard version or higher of SQL-Server on NT or 2000)<br>3) Create Full Text Catalog for your table and activate it<br>&nbsp;&nbsp;&nbsp;(read &lt;<A HREF="

http://www.microsoft.com/technet/sql/engquer.asp?a=printable&gt"

TARGET="_new">

http://www.microsoft.com/technet/sql/engquer.asp?a=printable&gt</A>;)&nbsp;&nbsp;&nbsp;<br>This

article is about English Queries as well as Full text Indexing.&nbsp;&nbsp;It is very helpful.<br><br><br>You can use a Full Text Index and Index the columns you are going to search against.&nbsp;&nbsp;&nbsp;You can then perform your searches with flexibility.&nbsp;&nbsp;For example:<br><br><b><br>SELECT * from [TableName] WHERE CONTAINS([ColumnName], 'ISABOUT(Colinsworth WEIGHT (0.9), Collinsworth WEIGHT (0.6), Collins WEIGHT (0.2))')<br></b><br><br>This will give the words a weight that you can sort by rank later on.<br><br>The final command will look like:<br><br><b><br>SELECT k.rank, e.* FROM [TableName] AS e <br>INNER JOIN CONTAINSTABLE([TableName],[ColumnName], 'ISABOUT(Colinsworth WEIGHT (0.9), Collinsworth WEIGHT (0.6), Collins WEIGHT (0.2))') AS k<br>ON k.[key] = e.[EmployeeID ORDER BY k.Rank DESC<br></b><br><br>Now, I is assumed that your [TableName] has a field called EmployeeID&nbsp;&nbsp;AND that it is a unique key.<br><br>I think this should solve your problem<br><br>Ghamdan Al-Anesi<br>Email me at:&nbsp;&nbsp;<A HREF="mailto:anesi@cyrsh.com">anesi@cyrsh.com</A><br><br><br>

 

[jwelch]

this really doesn't help because how am i supposed to know each variation of the user input and then put that into a sql statement? How am i suppose to figure out colinsworth, collins, etc from the user input of 'collinsworth'?<br>

 

[bigscouse]

To get the approprate variants for your query you could start with somethin like this: -<br><br>Select distinct last_name <br>from name_table<br>where last_name like 'Col%';<br><br>You would then have to weight each name. <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>