Installing and Using FuzzyStrMatch
Out of the box, PostgreSQL has some amazing extensions that are free to install and use for fuzzy matching strings which go beyond the abilities of the text search functionality in PostgreSQL. Using these extensions puts the capabilities of fuzzy matching on strings within SQL queries.
First, it is necessary to install the fuzzystrmatch contribution on the PostgreSQL host. On Ubuntu, this is as simple as:
sudo apt-get install postgresql postgresql-contrib
Next, to make the contributions accessible to this PostgreSQL instance, it is necessary to restart PostgreSQL.
The third step is to create the extension by issuing
At this point, there are ten new functions available to the database: difference, dmetaphone, dmetaphone_alt, levenshtein (two versions), levenshtein_less_equal (again, two versions), metaphone, soundex and text_soundex.
This simple example illustrates using the levenshtein function:
select levenshetin('foo', 'foobar');
...and results in the answer 3, for there are three edits necessary to transform 'foo' into 'foobar'.
This example SQL replicates the functionality of the GMSStrComp I wrote in Java to provide a percentage value for the similarity of two strings as explained in depth in the Levenshtein Fuzzy String Match post:
Select 1.0 - (levenshtein('foo', 'foobar')::float / greatest(length('foo'), length('foobar')))
Which results in the expected value of 0.5, hence 50% matching; which is exactly how much 'foo' matches 'foobar'.