I had the same problem of getting the dll to work with 2008. I'm in the process of converting a 2000 db to 2008 and I used that xp_NYSIIS a lot. Sooooo I've decided to just write a function that will do it based on the algorithm. Here is a first draft and isn't fully tested. It's also based on the original algorithm and not the modified one. Gonna work on that next. Unfortunately I've found inconsistent results with some of the online tools that I've been using to test this (http://www.dropby.com/NYSIIS.html). Anyway... it's a start for those that want to use it:
Declare
@Word varchar(50),
@FirstCharacter char(1),
@Length int,
@LetterPosition int,
@PreviousPosition int,
@NextPosition int,
@IteratedWord varchar(50)
SET @Word = 'Alexander'
SET @Word = replace(@Word,' ','')
SET @Word = rtrim(ltrim(@Word))
SET @Word = Upper(@Word)
SET @Length = len(@Word)
/*
1.Transcode first characters of name:
MAC»MCC
KN»NN
K»C
PH»FF
PF»FF
SCH»SSS
*/
IF left(@Word,3) = 'MAC' BEGIN SET @Word = 'MCC' + right(@Word,@Length-3) END
IF left(@Word,2) = 'KN' BEGIN SET @Word = 'NN' + right(@Word,@Length-2) END
IF left(@Word,1) = 'K' BEGIN SET @Word = 'C' + right(@Word,@Length-1) END
IF left(@Word,2) in ('PH','PF') BEGIN SET @Word = 'FF' + right(@Word,@Length-2) END
IF left(@Word,3) = 'SCH' BEGIN SET @Word = 'SSS' + right(@Word,@Length-3) END
PRINT '1: ' + @Word
/* 2.Transcode last characters of name:
EE, IE»Y
DT,RT,RD,NT,ND»D */
IF right(@Word,2) in ('EE','IE') BEGIN SET @Word = substring(@Word,1,@Length-2) + 'Y' END
IF right(@Word,2) in ('DT','RT','RD','NT','ND') BEGIN SET @Word = substring(@Word,1,@Length-2) + 'D' END
SET @Length = len(@Word)
PRINT '2: ' + @Word
/*3.First character of key = first character of name. */
SET @FirstCharacter = left(@Word,1)
PRINT '3: ' + @FirstCharacter
--Set @Word to remaining characters for further processing
SET @Word = right(@Word,@Length-1)
SET @Length = len(@Word)
PRINT 'Trimmed of First Character: ' + @Word
/*
4.Transcode remaining characters by following these rules, incrementing by one character each time:
EV»AFelse A,E,I,O,U » A
Q»G
Z»S
M»N
KN»Nelse K » C
SCH»SSS
PH»FF
H»If previous or next is nonvowel, previous
W»If previous is vowel, previous
Add current to key if current != last key character
*/
SET @Word = replace(@Word,'EV','AF')
SET @Word = replace(@Word,'E','A')
SET @Word = replace(@Word,'I','A')
SET @Word = replace(@Word,'O','A')
SET @Word = replace(@Word,'U','A')
SET @Word = replace(@Word,'Q','G')
SET @Word = replace(@Word,'Z','S')
SET @Word = replace(@Word,'M','N')
SET @Word = replace(@Word,'KN','N')
SET @Word = replace(@Word,'K','C')
SET @Word = replace(@Word,'SCH','SSS')
SET @Word = replace(@Word,'PH','FF')
--Iterate through each letter for "H" conversion
SET @IteratedWord = @Word
PRINT 'Iterated word: ' + @IteratedWord
SET @LetterPosition = 1
PRINT '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
While @LetterPosition <= @Length
BEGIN
SET @PreviousPosition = Case When @LetterPosition > 1 Then @LetterPosition-1 Else 1 END
SET @NextPosition = Case When @LetterPosition < @Length Then @LetterPosition+1 Else @Length END
IF substring(@IteratedWord,@LetterPosition,1) = 'H' and
(substring(@IteratedWord,@PreviousPosition,1) not in ('A','E','I','O','U') or
substring(@IteratedWord,@NextPosition,1) not in ('A','E','I','O','U'))
BEGIN
PRINT 'STUFF H!' + ' ' + @Word
SET @Word = stuff(@Word,@LetterPosition,1,'')
PRINT 'New Word: ' + isnull(@Word,'Null')
END
SET @LetterPosition = @LetterPosition + 1
SET @Length = len(@Word)
END
--Iterate through each letter for "W" conversion
SET @IteratedWord = @Word
PRINT 'Iterated word: ' + @IteratedWord
SET @LetterPosition = 1
PRINT '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
While @LetterPosition <= @Length
BEGIN
SET @PreviousPosition = Case When @LetterPosition > 1 Then @LetterPosition-1 Else 1 END
SET @NextPosition = Case When @LetterPosition < @Length Then @LetterPosition+1 Else @Length END
SET @Length = len(@Word)
IF substring(@IteratedWord,@LetterPosition,1) = 'W' and
substring(@IteratedWord,@PreviousPosition,1) in ('A','E','I','O','U')
BEGIN
PRINT 'STUFF W!' + ' ' + @Word
SET @Word = stuff(@Word,@LetterPosition,1,'')
PRINT 'New Word: ' + isnull(@Word,'Null')
END
SET @LetterPosition = @LetterPosition + 1
SET @Length = len(@Word)
END
/* 5. If last character is S, remove it */
IF right(@Word,1) = 'S'
BEGIN
PRINT 'Remove last letter S'
SET @Word = left(@Word,@Length-1)
SET @Length = len(@Word)
END
PRINT '5: ' + @Word
/* 6.If last characters are AY, replace with Y */
IF right(@Word,2) = 'AY'
BEGIN
PRINT 'Remove last characters AY'
SET @Word = stuff(@Word,@Length-1,2,'Y')
SET @Length = len(@Word)
END
PRINT '6: ' + @Word
/* 7.If last character is A, remove it */
IF right(@Word,1) = 'A'
BEGIN
PRINT 'Remove last letter A'
SET @Word = left(@Word,@Length-1)
SET @Length = len(@Word)
END
PRINT '7: ' + @Word
/* 8.Collapse all strings of repeated characters */
PRINT 'Collapse repeated characters...'
SET @LetterPosition = 1
While @LetterPosition < @Length
BEGIN
IF substring(@Word,@LetterPosition,1) = substring(@Word,@LetterPosition+1,1)
BEGIN
PRINT 'Next Letter Matchs. Remove Letter.'
SET @Word = stuff(@Word,@LetterPosition+1,1,'')
END
IF substring(@Word,@LetterPosition,1) <> substring(@Word,@LetterPosition+1,1)
BEGIN
PRINT 'Next Letter does not match. Advance'
SET @LetterPosition = @LetterPosition + 1
END
SET @Length = len(@Word)
END
PRINT '8: ' + @Word
/*9.Add original first character of name as first character of key*/
SET @Word = @FirstCharacter + @Word
PRINT '9: ' + @Word
--Only supposed to use first six characters but display the rest
PRINT left(@Word,6) + Case When @Length > 6 Then '['+ substring(@Word,7,@Length-5)+']' Else '' END