> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_fuzzystrmatch extension for MySQL

> The vsql_fuzzystrmatch extension adds Soundex, Metaphone, Double Metaphone, and Levenshtein edit distance to MySQL for name matching and deduplication.

`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](https://github.com/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:

```sql theme={null}
INSTALL EXTENSION vsql_fuzzystrmatch;
```

Confirm it is there:

```sql theme={null}
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](https://github.com/villagesql/vsql-fuzzystrmatch).

## 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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
SELECT metaphone('Thompson', 10) AS metaphone, dmetaphone('Thompson') AS dmetaphone;
```

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

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

<Note>
  `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".
</Note>

## See also

* [Fuzzy string matching in MySQL](/docs/guides/fuzzy-string-matching) — choosing between phonetic codes and edit distance
* [Trigram similarity](/docs/guides/trigram-similarity) — ranking partial matches instead of scoring pairs
* [Install extensions](/docs/mysql-9.7/stable/install) — how `INSTALL EXTENSION` works and where the server looks for a bundle
* [Available extensions](/docs/mysql-9.7/stable/extensions) — the full catalog
* [villagesql/vsql-fuzzystrmatch](https://github.com/villagesql/vsql-fuzzystrmatch) — source, build instructions, and the known limitations