0

I'm trying to search a table for users with similar names like John Smith and Joan Smith.

I tried this SQL statement but I think it is so simple and will not give me an accurate result

SELECT * FROM Profile Where DIFFERENCE(profile.name, 'John Smith') >=2

Thanks in advance

Daina Hodges
  • 823
  • 3
  • 12
  • 37
  • I'd take a look at full text indexing here: https://www.developer.com/db/article.php/3446891 – John Bell Mar 19 '18 at 12:21
  • DIFFERENCE is not very good for matching. Search on Levenshtein distance. – paparazzo Mar 19 '18 at 12:24
  • DIFFERENCE only works on SOUNDEX() values. – MJH Mar 19 '18 at 12:28
  • see https://stackoverflow.com/questions/560709/levenshtein-distance-in-t-sql/48116864#48116864 – paparazzo Mar 19 '18 at 12:45
  • In my opinion `Soundex()` is not really a viable option as it would put all these names into ONE basket: `John Smith`, `Jon Smith`, `Joan Smith`, `Jane Smith`, `John Smitter`, `Joan Smooth`, `Joan Smuther`, `Joan Sitter`, and even `Joanne Smolder`. I'd personally go with this solution: http://blog.softwx.net/2015/01/optimizing-damerau-levenshtein_19.html Alternatively, you can also keep a list of similar names or write your own adaptation of the `Soundex()` function in a UDF (as many have done before). – Ralph Mar 19 '18 at 13:42

0 Answers0