vsql_fuzzystrmatch extension for MySQL

vsql_fuzzystrmatch is a port of PostgreSQL’s fuzzystrmatch extension. It answers two different questions about a pair of strings: do they sound alike, and how many edits separate them. Use it for name matching, spelling correction, and deduplicating records that were typed by hand.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-fuzzystrmatch
License	GPL-2.0

Install

vsql_fuzzystrmatch.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_fuzzystrmatch;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_fuzzystrmatch';

+--------------------+
| EXTENSION_NAME     |
+--------------------+
| vsql_fuzzystrmatch |
+--------------------+

To build it yourself, follow the build instructions in the repository.

What it adds

Function	Returns	What it does
`vsql_fuzzystrmatch.soundex(s)`	text	The Soundex code, so names that sound alike share a value. Qualify this one, see the note below
`difference(a, b)`	int	How many of the four Soundex positions match, 0 through 4
`levenshtein(a, b)`	int	The edit distance
`levenshtein_cost(a, b, ins, del, sub)`	int	The same, with your own cost per operation
`levenshtein_less_equal(a, b, max)`	int	The distance, giving up once it passes `max` and returning `max` plus 1
`levenshtein_less_equal_cost(a, b, ins, del, sub, max)`	int	Both of the above together
`metaphone(s, length)`	text	The Metaphone code, truncated to `length`
`dmetaphone(s)`	text	The primary Double Metaphone code
`dmetaphone_alt(s)`	text	The alternate code, falling back to the primary when there is none

Example

Two spellings of the same name produce the same Soundex code, and difference scores the match out of 4:

SELECT vsql_fuzzystrmatch.soundex('Robert') AS robert,
       vsql_fuzzystrmatch.soundex('Rupert') AS rupert,
       difference('Robert', 'Rupert') AS same_sound;

+--------+--------+------------+
| robert | rupert | same_sound |
+--------+--------+------------+
| R163   | R163   |          4 |
+--------+--------+------------+

Edit distance counts the changes instead:

SELECT levenshtein('kitten', 'sitting') AS edits,
       levenshtein_less_equal('kitten', 'sitting', 2) AS capped_at_2;

+-------+-------------+
| edits | capped_at_2 |
+-------+-------------+
|     3 |           3 |
+-------+-------------+

Metaphone handles the sounds that spelling hides, such as the Th in Thompson:

SELECT metaphone('Thompson', 10) AS metaphone, dmetaphone('Thompson') AS dmetaphone;

+-----------+------------+
| metaphone | dmetaphone |
+-----------+------------+
| 0MPSN     | TMPSN      |
+-----------+------------+

MySQL has a SOUNDEX() of its own, and the built-in wins when you write the name unqualified. The two disagree: SOUNDEX('Ashcraft') returns A2613, while vsql_fuzzystrmatch.soundex('Ashcraft') returns A261. MySQL’s version is documented as returning a string of any length, and the extension’s follows the four-character Soundex definition. Qualify the call with the extension name to get the one you mean. No other function here is shadowed.

levenshtein_less_equal stops as soon as the true distance passes the limit you give it, and then returns the limit plus 1, whatever the true distance is. With a limit of 2 it returns 3 for a pair 3 apart and 3 for a pair 7 apart. Read the limit plus 1 as “further apart than you asked about”.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

Example

See also

​Install

​What it adds

​Example

​See also

Install

What it adds

Example

See also