String matching using function

I am looking for a way to fuzzy match strings (in my case contact names) to see where there might be possible duplicates in the database. The 'duplicates' are actually cases where the names are very similar, as each row will have unique data.

I have been looking around and think that this: JaroWinkler Function would best suit my needs, which works quite well on small sets of strings.

However, I am looking to compare about 260,000 distinct strings, and want to see if there is a way to avoid checking through all possible combinations (as this would give me around 29 billion rows of checking).

As it stands the query I am using for a small sample set:

CREATE TABLE #data
(
    ROW INT IDENTITY (1,1)
    ,string VARCHAR(50)
)

INSERT INTO #data SELECT 'Watts' AS string
UNION ALL SELECT 'Burns'
UNION ALL SELECT 'McLaughlan'
UNION ALL SELECT 'Darry'
UNION ALL SELECT 'Storie'
UNION ALL SELECT 'Mcluangan'
UNION ALL SELECT  'Burnsysx'

SELECT 
data1.string as string1
,data1.row as row1
,data2.string as string2
,data2.row as row2
,dbo.JaroWinkler(data1.string,data2.string) as correlation
from #data data1
CROSS JOIN #data data2
WHERE data1.row < data2.row

Which for this sample data returns 21 rows, but I am only interested in rows where the correlation is above 0.7, so the majority of these can be removed from the output, and if possible not even used as a comparison point.

So for the example data above, I would want to return the following rows:

string1 row1    string2 row2    correlation
McLaughlan  3   Mcluangan     6 0.8962954
Burns            2  Burnsysx  7 0.874999125

I know that using inequality triangular joins is not a good idea, so would using a cursor be a bette开发者_如何转开发r one? I do unfortunately need to check all records against each other to make sure duplicates don't exist.

For the purposes of testing, the Difference(data1.string,data2.string) could be used, filtering only cases where the value = 4 (so that I can at least get a sense of how best to move forwards with this)!!

Thanks!

The fuzzy logic feature in SSIS might be worth a shot, if you haven't tried it yet. It might be more performant than the query you have and has more "tweakable" parameters. It is relatively easy to set up.

http://msdn.microsoft.com/en-us/magazine/cc163731.aspx

If you are trying to find duplicate names, have you considered using the built-in SOUNDEX() function to find matches?