SQLTeam.com | Weblogs | Forums

Update table based on matching 2 tables

sql2014

#1

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.


#2

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
;

#3

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.