Soundex Discrepancy between SQL and VFP

[jbailey268]

Why would the soundex of "PFIZER" be P260 in VFP8 and also in .NET - and why then would the native SOUNDEX function in Microsoft SQL 2000 return P126.

Interestingly; the soundex of "PFIZER" in Borland dBASEV for DOS was P126. Ugh!

This is not just an anecdotal observation but could cause some data mismatches when in fact they are not mis-matches. We often compare data between the 2 systems.

 

[alvechurchdata]

You don't want to know this but there's more than one Soundex algorithm. I've heard of "Original", "Miracode", "Knuth", "Russel", "SQL Server" and there may be more.

I know there's one which was optimised for genealogical research on Jewish surnames and a quick Google for "jewish soundex" has just found up "Daitch-Mokotoff" at

http://www.avotaynu.com/soundex.html.

Apologies for bringing bad news. Your best bet is to write your own consistent algorithm for each database.



Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

I've used the old dBASE version of Soundex as well as the Foxbase/Foxpro version, and neither of them gave results that the user was happy with.

I once wrote my own Soundex in Foxbase, and it worked much better, but that was nearly 20 years ago and I no longer have the source code. It was loosely based on the Knuth algorithm, which I believe is the most widely used.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Mike Gagnon]

Take a look at faq184-4340


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[jbailey268]

Thanks for your responses, all of you. Especially MikeLewis - I don't know when you have time to work, you almost always answer my posts and others as well. You must be amazingly bright and patient.

I know there are different soundex methods, we were researching some in house. Regarding soundex: My quizzical nature is such that I can not comprehend why VFP would employ one method and SQL would employ a different method, without any kind of setting indicator. This leads one to think one is "better/newer" than another. Since .NET and all versions of VFP yield the same result, this implies this is the method of choice. However, this still occassions the question why would Microsoft build SQL 2000 with a "different" perhaps inferior/older Soundex() function? That question has not been addressed here. Perhaps the same question should be sent to the MS SQL forum. Any thughts?