2

I would like to allow my app to search for names and return similar first names. I.e. if the user searches for John, Jonathan's should be returned as well.

A Soundex search does not seem to do this. Are there any alternatives other than storing a dictionary of related names?

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
MillinMo
  • 395
  • 1
  • 7
  • 21
  • One possibility may be to trim the words to the shorter length - in this example soundex of both with four characters gives the same value – kaj Feb 23 '12 at 13:01
  • @KAJ But that's just one case with those specific names. I think this comes down to people having more information about the application domain that basic SQL functions will not. – Yuck Feb 23 '12 at 13:02
  • Hence why I wasn't offering it as an answer :-) – kaj Feb 23 '12 at 13:10

2 Answers2

1

No, there is not - because you look for an interpretation of similiarity, not a similar sounding thing. Keeping alternatives is your only choice.

TomTom
  • 61,059
  • 10
  • 88
  • 148
1

You should look at Levenshtein Edit Distance, and look for strings with low edit distance comparing to target string.

Here is more info and implementation of it for SQL Server

http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx

Edit distance shows minimum number of edits (character deletions, replacements or insertions) you need to make to a string to get your target string.

So for example 'apple' and 'aple' will have an edit distance of 1 (deletion of 'p');

3rd party edit

The code from kodyaz.com points to a posting from Arnold Fribble on the sqlteam.com/forums You can find the code in this question from 2009

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
gintas
  • 2,118
  • 1
  • 18
  • 28