Oh my... I haven't tested any of these on 64-bit Win2003 or SQL 2005. They were only tested on SQL 2000, 32-bit. XP's on SQL 2005 have been deprecated but are still allowed... for now. A better bet might be to convert the routines to .NET assemblies for SQL 2K5. The downloads contain the C++ source code for these routines. If you were using SQL 2000, I'd recommend using the routines here: http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp, with more optimizations and additional routines added.
Um... I checked the example given under the NYSIIS and they don't all return JANSAN
Johnson J525 JANSAN Johnsen J525 JANSAN Johanson J525 JAHANSAN Johannsen J525 JAHANSAN
Yes, unfortunately the SQL Server SOUNDEX() function stops encoding a name when it encounters a non-alphabetic character. So "DE LA POINTE", "DE LA SOUL", etc. will all encode to "D000". One work-around with Soundex is to eliminate all spaces from a name before encoding with the REPLACE() function:
SELECT
Another option (that NARA recommends) is to eliminate common prefixes like 'DE', 'LA', 'VAN', 'CON', etc. from the name before Soundex-encoding it. See http://www.archives.gov/publications/general-info-leaflets/55.html for more info. Also note that the MS SOUNDEX() function does not follow the official NARA Soundex standard, and may return results that don't match up with external data sources that encode correctly.
This NYSIIS implementation ignores invalid characters (spaces, etc.) in the name and encodes until it runs out of alphabetic characters. Also see http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp for the update to this set. The update includes NYSIIS, Double-Metaphone, Celko Soundex, Daitch-Mokotoff Soundex, Levenshtein Edit Distance and Jaro-Winkler Distance functions.
