I have 2 tables. FullName and InputName. FullName has a primary key column Id and Name column. InputName has no index and consists of a InName column only. I want to update the data in the InputName table based on the values on the FullName table.
So, in the InputName table there might be Tony D Jones, in the FullName table Tony D Jones is shown as T D Jones. I want to update the InputName table to match that in the FullName table. Where a sufficiently close match is not found, no action is taken. How would I do that please.
The discrepancy between the names in the tables arises because I am scraping the data from different sources from the web, all of which record the same people in slightly different ways and I need to make regular the names in the dbase.
Thanks for all and any help.
This might get you some of the way:
with fullname(id,[name])
as ( select 1,'Tony D Jones'
union all select 2,'Tina D Jones'
union all select 3,'Indiana Jones'
)
,inputname(InName)
as ( select 'T D Jones'
union all select 'I Jones'
)
,fullname_indexes([name],i1,i2)
as (select [name]
,1
,charindex(' ',[name]+' ')
from fullname
group by [name]
union all
select [name]
,i2+1
,charindex(' ',[name]+' ',i2+1)
from fullname_indexes
where charindex(' ',[name]+' ',i2+1)>0
)
,fullname_splitparts([name],namepart,rn)
as (select [name]
,substring([name],i1,i2-i1)
,row_number() over(partition by [name] order by i1 desc)
from fullname_indexes
)
,fullname_parts([name],nameparts)
as (select [name]
,stuff((select ' '
+case when b.rn=1 then b.namepart else substring(b.namepart,1,1) end
from fullname_splitparts as b
where b.[name]=a.[name]
order by b.rn desc
for xml path('')
)
,1
,1
,''
)
from fullname_splitparts as a
group by [name]
)
select a.inname
,b.[name]
,count(a.inname) over(partition by a.inname) as matches
from inputname as a
left outer join fullname_parts as b
on b.nameparts=a.inname
;
Thank you so much for taking the time and trouble to work out a solution for me. I didn't fully realise what I was asking. Thank you again.