Abu Dina (1/23/2013)
Hi Chris hope you're well!Thanks for the thread. I shall go and have a thorough read in a mo.
I'm really grateful you brought my attention to iTVF Last year.
I've been using it with great results and when this is finished it will end up wrapped inside an iTFV.
BTW, do you find my requirement as bizarre as Sean thinks lol.. I'm beginning to worry now...
Nah mate I've seen this before, it's a method used by some of the professional matching packages. MatchIT, IIRC.
BTW here's probably the "right way" to do what you want:
SELECT Forename, Surname, x.PhoneticNameKey
FROM #Names
CROSS APPLY (
SELECT PhoneticNameKey =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
Surname COLLATE Latin1_General_BIN,
'A','y'),'B','b'),'C','k'),'D','d'),'E','y'),'F','f'),'G','g'),'H','h'),'NI','m')
,'I','y'),'J','j'),'KN','n'),'K','k'),'L','l'),'M','m'),'NT','m'),'N','n'),'O','y')
) x
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]