PHP MySql - SELECT其中similar_text（）> x

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...

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.

To use similar_text, you will need to get cross join the two tables, get an array of the outcome and use the similar_text function in the result array. If you do that, you could save the result in a third auxiliar table.

However, you could use MySQL's native function SOUNDEX(), will which work, or create a Levehnstein function.