PHP MySql - SELECT其中similar_text（）> x

Question

I have two tables called reference and list. Both have two fields: id and name.

reference:
ref_id, ref_name
1, john
2, georges
etc...

list:
lst_id, lst_name
1, steward
2, james
3, john
4, jones
5, georges
etc...

I would like to select the name from both tables where similar_text()>60. Here below the output I would like to have:

ref_name, lst_name, similar_text()
john, john, 100
john, jones, 67
georges, georges, 100

Note that steward and james should be left out as the similar_text() is below 60.

Hope someone can help. Thank you in advance for your relies. Cheers. Marc. Oh, by the way, I am open to better solutions to achieve my goal...

Answer 1

You will most likely need levenshtein in mysql. This will allow you to calculate the differences in strings.

you could then do something like:

SELECT ref_name, lst_name, LEVENSHTEIN_RATIO( ref_name, lst_name ) as textDiff
FROM reference, list
HAVING textDiff > 60;

Or something similar.

NOTE: When working on large datasets this will most definitely have performance issues.