On Thu, 30 Dec 2004 10:03:02 -0800, Johnny wrote:

> I have a customer with two companies. Both companies stock the same items
> but not all of the item numbers are the same. They would like to have the
> same item numbers in both company's and are looking for some help in
> identifying the possible matches. For example, company A has item 12345 and
> company B has the same item but calls it 98765. The description assigned to
> both of them are different as well but very close. For example, item 12345
> has a description of "ITEM-NUMBER" and item 98765 has a description of "ITEM
> NUMBER". How can I create a sql statement that looks at company A's item
> numbers as the baseline and provides a list of the closest matches of company
> B's items?

"Ross Presser" wrote:

> On Thu, 30 Dec 2004 10:03:02 -0800, Johnny wrote:
>
> > I have a customer with two companies. Both companies stock the same items
> > but not all of the item numbers are the same. They would like to have the
> > same item numbers in both company's and are looking for some help in
> > identifying the possible matches. For example, company A has item 12345 and
> > company B has the same item but calls it 98765. The description assigned to
> > both of them are different as well but very close. For example, item 12345
> > has a description of "ITEM-NUMBER" and item 98765 has a description of "ITEM
> > NUMBER". How can I create a sql statement that looks at company A's item
> > numbers as the baseline and provides a list of the closest matches of company
> > B's items?
>
> Look at the levenshtein distance function
> http://www.merriampark.com/ldtsql.htm
>
> It returns zero for identical strings, and in general, smaller integers for
> string pairs that are more similar, and larger integers for those that are
> larger.
>
> Another possibility is the soundex algorithm, which groups together words
> that are pronounced alike. In my experience this is only really useful for
> human surname matching, and always requires a lot of manual effort.