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 #StringInTable
from cte
order by  RowID

select * from #StringInTable

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

if object_id('tempdb..#Names') is not null
	drop table #Names

if 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 all
select 'TEST', 'Knuckle' union all
select '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 #StringInTable
from cte
order by  RowID

select * from #StringInTable

select 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

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