Pattern matching sql algorithm

Question

Table 1
Account ID   Account name 
Missing      Agreete NV.


Table 2 
Account ID   Account name 
XXX4546778   Agreete

I have to pouplate all the Account IDs in Table 1 based on the best match looking at the account name in both tables .

I thought about like, patindex & soundex.

Thinking about it I was thinking compare full string, if no match then compare full string -1 , if not match then then compare full string -2 until you get a match.

However someone must have came up with Pattern matching sql algorithm that will do this with a low error rate . Any ideas ?

james, this is a great question, i am eager to see possible solutions for this — Alex Gordon
– Alex Gordon, Commented Jul 27, 2012 at 15:41

Chris Moutray · Accepted Answer · 2012-07-27 19:59:08Z

1

Perhaps im missing the point of your question but it seems to me you have the exact match like you said

update t1
set [account id] = t2.[account id]
from table1 t1
inner join table2 t2 on t1.[account name] = t2.[account name]
where t1.[account id] = 'missing'

And you have the partial match

update t1
set [account id] = t2.[account id]
from table1 t1
inner join table2 t2 on t1.[account name] like t2.[account name] + '%'
where t1.[account id] = 'missing'

run in that order...

answered Jul 27, 2012 at 19:59

Chris Moutray

18.4k7 gold badges49 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pattern matching sql algorithm

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related