InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 13:43:00 GMT20http://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspx[quote][b]Abu Dina (1/23/2013)[/b][hr]Lol Chris you scare me.... And embarrass me at the same time.Simple solution....This is another addition to the various phonetic algorithm implementations I have. Always hoping for better match keys lol!Thanks.[/quote]Hey geezer! I'll tell you what's scary - it's taken 20 years for me to be able to figure out an answer to your question [i]on the second shot[/i], but there are folks at almost every gig I go to, who can do better, after only three years' playing with SQL. There are some [i]very[/i] talented players on the field and we all chase the same jobs...Thu, 24 Jan 2013 15:19:39 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxI've been trying all morning to get a set of REPLACES to work but there are too many rules to apply.I may have to go down the CLR route for this.Thu, 24 Jan 2013 08:57:23 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxLol Chris you scare me.... And embarrass me at the same time.Simple solution....This is another addition to the various phonetic algorithm implementations I have. Always hoping for better match keys lol!Thanks.Wed, 23 Jan 2013 14:53:49 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspx[quote][b]Abu Dina (1/23/2013)[/b][hr]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...[/quote]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:[code="sql"]SELECT Forename, Surname, x.PhoneticNameKeyFROM #NamesCROSS 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[/code]Wed, 23 Jan 2013 14:05:27 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxHi 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...Wed, 23 Jan 2013 13:44:28 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxHaha Sean..... I stopped at O as I didn't want to give away how other keys are created. This is not as bizarre as you think. A lot of work has gone into this. And when it's finished it will help me a lot. Not tried your solution but will do first thing tomorrow morning.Cheers for the effort.Wed, 23 Jan 2013 13:40:17 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxHow are you doing, Abu Dina?As luck would have it, one or two of us have been experimenting with exactly this requirement [url=http://www.sqlservercentral.com/Forums/Topic1408313-391-1.aspx]here on this thread[/url], which will continue to run for a day or two. Enjoy, and pick your favourite from the mix. Each method has something different to offer (or bitch about, depending on your POV).The fastest method by far - and it seems a good fit to your requirement - is the iTVF in which a table variable is hard coded with the find and replace characters. If it's not there (it doesn't work with the test harness), I'll post it tomorrow.CheersChrisMWed, 23 Jan 2013 13:00:26 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxWhat a completely bizarre requirement. However you did provide very easy to consume data!!! A solution here is not really too bad. Just add the next character into your CTE and you then have access to the "next" character.[code]if object_id('tempdb..#Names') is not null drop table #Namesif object_id('tempdb..#StringInTable') is not null drop table #StringInTable create table #Names (Forename nvarchar(50), Surname nvarchar(50), PhoneticNameKey nvarchar(20))insert into #Names(Forename, Surname)select 'JOSE', 'ANTINORI' union allselect 'TEST', 'Knuckle' union allselect 'ITest', 'Nint'select * from #Names;with cte as( select substring(Surname, 1, 1) as Chars, substring(Surname, 2, 1) as Char2, stuff(Surname, 1, 1, '') as Surname, 1 as RowID from #Names union all select substring(Surname, 1, 1) as Chars, substring(Surname, 2, 1) as Char2, stuff(Surname, 1, 1, '') as data, RowID + 1 as RowID from cte where len(Surname) > 0)select RowID, Chars, Char2 into #StringInTablefrom cteorder by RowIDselect * from #StringInTableselect chars, char2, case chars when 'A' then 'y' when 'B' then 'b' when 'C' then 'k' when 'D' then 'd' when 'E' then 'y' when 'F' then 'f' when 'G' then 'g' when 'H' then 'h' when 'I' then 'y' when 'J' then 'j' when 'K' then case when char2 = 'N' then 'n' else 'k' end -- but if K is followed by N then should become n when 'L' then 'l' when 'M' then 'm' when 'N' then case when char2 in ('I', 'T') then 'm' else 'n' end -- if N is followed by I or T then set to m when 'O' then 'y' end from #StringInTable[/code]Wed, 23 Jan 2013 12:37:21 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410712-392-1.aspxPlease consider the following example:[code="sql"]create table #Names (Forename nvarchar(50), Surname nvarchar(50), PhoneticNameKey nvarchar(20))insert into #Names(Forename, Surname)select 'JOSE', 'ANTINORI' select * from #Names;with cte as( select substring(Surname, 1, 1) as Chars, stuff(Surname, 1, 1, '') as Surname, 1 as RowID from #Names union all select substring(Surname, 1, 1) as Chars, stuff(Surname, 1, 1, '') as data, RowID + 1 as RowID from cte where len(Surname) > 0)select RowID, Chars into #StringInTablefrom cteorder by RowIDselect * from #StringInTable[/code]The idea here is to perform a series of replaces on the characters to produce a phonetic key. So I started off with something like this:[code="sql"]select case chars when 'A' then 'y' when 'B' then 'b' when 'C' then 'k' when 'D' then 'd' when 'E' then 'y' when 'F' then 'f' when 'G' then 'g' when 'H' then 'h' when 'I' then 'y' when 'J' then 'j' when 'K' then 'k' -- but if K is followed by N then should become n when 'L' then 'l' when 'M' then 'm' when 'N' then 'n' -- if N is followed by I or T then set to m when 'O' then 'y' end from #StringInTable[/code]But as you can see, for some characters, I need to check the next character to decide on the phonetic character to use. Can someone help me with this?Thanks in advance.Wed, 23 Jan 2013 10:54:07 GMTAbu Dina