Fuzzy logic for duplicate identification?

[Yashgt]

Hi,

We have a table of users with about 10000 rows. We have to import a
large text file into this table. While doing so we would like to
ensure that if a row already exists, it should not be imported. Rather
that True/false logic, we need to apply Fuzzy logic here so that if we
find similarities in the name, address, ad a few other columns, we
treat the row as a duplicate.

Such possibly duplicate rows should be identified and logged to some
table.

What transformation should I prefer?

Thanks,
Yash

[Plamen Ratchev]

Re: Fuzzy logic for duplicate identification?

SQL Server has the SOUNDEX and DIFFERENCE functions:

http://msdn.microsoft.com/en-us/library/ms187384.aspx

However, the algorithm implemented is very simple and in many cases not good
enough. You may need to write your own soundex logic. Here is one algorithm
that is more advanced:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=519&lngWId=5

If you are performing the import via Integration Services, it does have
Fuzzy Lookup functionality:

http://www.sqlteam.com/article/using-fuzzy-lookup-transformations-in-sql-server-integration-services

http://msdn.microsoft.com/en-us/library/ms345128.aspx

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[--CELKO--]

Re: Fuzzy logic for duplicate identification?

>What transformation should I prefer? <<

If this is address data for mailing, then get a specialized package
from Group 1, Melissa Data, etc. They are very good at this.

Otherwise, the Levenshtein distance is pretty standard. It measures
how many changes have to be made to convert one string to the other.