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

# Phonetic Matching and Edit Distance in MySQL

> How to match strings by sound and edit distance in MySQL using VillageSQL's vsql_fuzzystrmatch extension — Soundex, Levenshtein, Metaphone, and Double Metaphone.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

> [!NOTE]
>
> This guide requires VillageSQL 0.0.4 or later.

Two strings that refer to the same thing — a person's name, a company, a place — often look different in practice. Misspellings, transliterations, transcription errors, and regional variations all produce variation that `=` and `LIKE` miss. VillageSQL's `vsql_fuzzystrmatch` extension provides four approaches: Soundex (sounds-alike grouping), Levenshtein edit distance (character-by-character cost), Metaphone (pronunciation-based encoding), and Double Metaphone (handles ambiguous pronunciations from multiple linguistic origins).

## Installation

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

The functions work unqualified. They can also be called as `vsql_fuzzystrmatch.soundex(...)`, qualifying by the owning extension's name — that is VEF name resolution, not a real schema, and `vsql_fuzzystrmatch` does not appear in `SHOW DATABASES`. All return NULL when any argument is NULL.

## Soundex

Soundex reduces an English word to a 4-character code based on how it sounds. Words with the same code are considered phonetic matches.

```sql theme={null}
SELECT vsql_fuzzystrmatch.soundex('Robert');   -- R163
SELECT vsql_fuzzystrmatch.soundex('Rupert');   -- R163
SELECT vsql_fuzzystrmatch.soundex('Smith');    -- S530
SELECT vsql_fuzzystrmatch.soundex('Smythe');   -- S530
```

`difference` returns a 0–4 score for how many Soundex code characters two strings share. 4 = identical codes.

```sql theme={null}
SELECT vsql_fuzzystrmatch.difference('Robert', 'Rupert');   -- 4
SELECT vsql_fuzzystrmatch.difference('Robert', 'Sylvia');   -- 0
```

**Use Soundex when** you want a simple sounds-alike grouping and your data is primarily English names. It's fast and easy to explain, but it can conflate unrelated names and misses non-English phonetics.

### Finding phonetic matches in a table

```sql theme={null}
SELECT name
FROM customers
WHERE vsql_fuzzystrmatch.soundex(name) = vsql_fuzzystrmatch.soundex('Smith')
ORDER BY name;
```

To find all names that are a close phonetic match (not necessarily identical codes):

```sql theme={null}
SELECT name, vsql_fuzzystrmatch.difference(name, 'Johnson') AS score
FROM customers
WHERE vsql_fuzzystrmatch.difference(name, 'Johnson') >= 3
ORDER BY score DESC;
```

## Levenshtein edit distance

Levenshtein counts the minimum number of single-character edits — insertions, deletions, substitutions — needed to turn one string into another.

```sql theme={null}
SELECT vsql_fuzzystrmatch.levenshtein('kitten', 'sitting');   -- 3
SELECT vsql_fuzzystrmatch.levenshtein('mysql', 'mysql');      -- 0
SELECT vsql_fuzzystrmatch.levenshtein('abc', 'xyz');          -- 3
```

**Use edit distance when** you want to match strings that differ by a small number of typos, and you want precise control over the distance threshold.

### Custom operation costs

`levenshtein_cost` lets you weight insertions, deletions, and substitutions differently:

```sql theme={null}
-- Substitutions cost 2, insertions and deletions cost 1
SELECT vsql_fuzzystrmatch.levenshtein_cost('abc', 'xyz', 1, 1, 2);  -- 6
```

### Early-exit threshold

`levenshtein_less_equal` short-circuits once the distance exceeds a threshold — more efficient for filtering. When the actual distance exceeds `max_d`, it returns `max_d + 1` instead of computing the full distance:

```sql theme={null}
-- Distance is 3, max_d is 5: returns real distance
SELECT vsql_fuzzystrmatch.levenshtein_less_equal('kitten', 'sitting', 5);   -- 3

-- Distance is 3, max_d is 2: cap fires, returns max_d + 1
SELECT vsql_fuzzystrmatch.levenshtein_less_equal('abc', 'xyz', 2);          -- 3 (= max_d + 1)
```

Combined with custom costs:

```sql theme={null}
SELECT vsql_fuzzystrmatch.levenshtein_less_equal_cost('abc', 'xyz', 1, 1, 1, 2);  -- 3
```

Both input strings must be ≤ 255 characters. Costs must be non-negative.

### Deduplication by edit distance

```sql theme={null}
SELECT a.name, b.name, vsql_fuzzystrmatch.levenshtein(a.name, b.name) AS dist
FROM companies a
JOIN companies b ON a.id < b.id
WHERE vsql_fuzzystrmatch.levenshtein_less_equal(a.name, b.name, 2) <= 2
ORDER BY dist;
```

## Metaphone

Metaphone applies more English pronunciation rules than Soundex and produces variable-length output. The `max_output_length` parameter caps the result.

```sql theme={null}
SELECT vsql_fuzzystrmatch.metaphone('Robert', 10);    -- RBRT
SELECT vsql_fuzzystrmatch.metaphone('Thompson', 10);  -- 0MPSN
SELECT vsql_fuzzystrmatch.metaphone('Robert', 2);     -- RB  (truncated)
```

**Use Metaphone when** Soundex produces too many false positives or misses matches for less common names. It handles more pronunciation patterns (TH → 0, silent letters, etc.).

## Double Metaphone

Double Metaphone handles ambiguous pronunciations from multiple linguistic origins — names of German, Slavic, Spanish, Hebrew, and other origins that get anglicized in different ways. It produces two codes: primary and alternate.

```sql theme={null}
SELECT vsql_fuzzystrmatch.dmetaphone('Robert');      -- RPRT
SELECT vsql_fuzzystrmatch.dmetaphone_alt('Robert');  -- RPRT  (no alternate)

SELECT vsql_fuzzystrmatch.dmetaphone('Schmidt');     -- XMT
SELECT vsql_fuzzystrmatch.dmetaphone_alt('Schmidt'); -- SMT   (alternate pronunciation)
```

When no alternate exists, `dmetaphone_alt` falls back to the primary code.

**Use Double Metaphone when** your data contains names from multiple linguistic backgrounds, or when Metaphone misses matches that a human would consider equivalent.

### Matching with both codes

```sql theme={null}
-- Match if either the primary or alternate code aligns
SELECT name
FROM customers
WHERE vsql_fuzzystrmatch.dmetaphone(name) = vsql_fuzzystrmatch.dmetaphone('Schmidt')
   OR vsql_fuzzystrmatch.dmetaphone_alt(name) = vsql_fuzzystrmatch.dmetaphone('Schmidt')
   OR vsql_fuzzystrmatch.dmetaphone(name) = vsql_fuzzystrmatch.dmetaphone_alt('Schmidt');
```

## Choosing an approach

| Situation | Recommended approach |
| - | - |
| English name sounds-alike grouping | Soundex |
| Typo tolerance with a distance budget | Levenshtein |
| Better English phonetics than Soundex | Metaphone |
| Names from mixed linguistic backgrounds | Double Metaphone |
| Similarity score rather than a threshold | [trgm\_similarity](https://villagesql.com/docs/en/guides/trigram-similarity.md) |

## Function reference

| Function | Returns | Notes |
| - | - | - |
| `soundex(text)` | TEXT | 4-char Soundex code |
| `difference(text, text)` | INT | 0–4 Soundex overlap score |
| `levenshtein(s, t)` | INT | Edit distance, all ops cost 1 |
| `levenshtein_cost(s, t, ins, del, sub)` | INT | Edit distance with custom costs |
| `levenshtein_less_equal(s, t, max_d)` | INT | Distance if ≤ max\_d; else max\_d+1 |
| `levenshtein_less_equal_cost(s, t, ins, del, sub, max_d)` | INT | Custom costs + early exit |
| `metaphone(text, max_len)` | TEXT | Metaphone code, truncated to max\_len |
| `dmetaphone(text)` | TEXT | Double Metaphone primary code |
| `dmetaphone_alt(text)` | TEXT | Double Metaphone alternate code |

## Known limitations

**No function overloading.** VEF registers functions by name only; two functions with the same name cannot coexist. Where PostgreSQL uses overloads (`levenshtein` with 2 vs 5 args), this extension uses distinct names (`levenshtein` vs `levenshtein_cost`).

**String length limit.** `levenshtein` inputs must be ≤ 255 characters.

## See also

* [How to find similar strings with trigram similarity in MySQL](https://villagesql.com/docs/en/guides/trigram-similarity.md)
* [How to implement full-text search in MySQL](https://villagesql.com/docs/en/guides/full-text-search.md)
* [How to manipulate and transform strings in SQL](https://villagesql.com/docs/en/guides/string-functions.md)
* [MySQL REGEXP](https://villagesql.com/docs/en/tutorial/regexp.md)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.