Fuzzy matching by using SimMetrics library

Question

I need some help here. How would, I create a simple SQL statement to select Names @userEnteredName with these functions. In other words, I want to get customer names from the customer table where the user typed in smyth and get back smith, smitty, etc....

... or with one word How do I use the bellow created functions to query a database table.

Thank you in advance for you help.

<code>declare @userEnteredLastName varchar(200); declare @userEnteredFirstName varchar(200); set @userEnteredLastName='smyth'; set @userEnteredFirstName='Jon';

SELECT * FROM Customer WHERE JaroWinkler(CustomerLastName, @userEnteredLastName) > .75 AND JaroWinkler(CustomerFirstName, @userEnteredFirstName) > .75</code>

I using the SimMetrics library for it located at SimMetrics

Answer 1

If you are using SQL Server 2008 and above (should work on 2005, I have not tested it)...

Try using a Table Value Function (TVF) in the form of CROSS APPLY to make this work. You can wrap the scalar function in a TVF to accomplish this, like so:

CREATE FUNCTION dbo.Jarowinklertvf(@firstword  NVARCHAR(255), 
                                   @secondword NVARCHAR(255)) 
returns TABLE 
AS 
    RETURN 
      (SELECT dbo.Jarowinkler(@firstword, @secondword) Score, 
              'JaroWinkler'                            Metric) 

Then call the function like so:

DECLARE @userEnteredLastName VARCHAR(200); 
DECLARE @userEnteredFirstName VARCHAR(200); 

SET @userEnteredLastName='smyth'; 
SET @userEnteredFirstName='Jon'; 

SELECT l.score LastNameScore, 
       f.score FirstNameScore, 
       i.* 
FROM   customer i 
       CROSS apply dbo.Jarowinklertvf(i.customerlastname, @userEnteredLastName) 
                   l 
       CROSS apply dbo.Jarowinklertvf(i.customerfirstname, @userEnteredFirstName 
                   ) f 
WHERE  l.score > .75 
       AND f.score > .75 
ORDER  BY 1 DESC 

I hope this helps.