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

# Trigram Similarity Search in MySQL

> How to find similar strings in MySQL using VillageSQL's vsql_trgm extension — similarity scoring, fuzzy matching, and word-level similarity based on the trigram model.

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

MySQL's built-in string functions — `LIKE`, `SOUNDEX`, `LEVENSHTEIN` — cover basic matching, but none of them give you a continuous similarity score you can sort or threshold on. VillageSQL's `vsql_trgm` extension adds trigram-based similarity: a number between 0 and 1 that measures how alike two strings are, so you can rank results and filter by similarity in a single query.

## How trigrams work

A trigram is a group of 3 consecutive characters. `vsql_trgm` lowercases the input, splits it on non-alphanumeric characters, pads each word with two leading spaces and one trailing space, then slides a 3-character window across each padded word. Similarity is how many trigrams the two inputs share, divided by the number of distinct trigrams across both — the size of their union, not the size of the larger set.

```
"cat" → pad to "  cat " → ["  c", " ca", "cat", "at "]
"car" → pad to "  car " → ["  c", " ca", "car", "ar "]
shared: 2, union: 4 + 4 − 2 = 6 → similarity 0.333
```

Two things to know about multi-word strings: they are **lowercased** before processing, so `"Cat"` and `"cat"` produce the same trigrams. They are also **split on non-alphanumeric characters**, so each word is padded and trigrammed independently — `"hello world"` produces trigrams from `"  hello "` and `"  world "` separately, not from a single padded string. There is no `"o w"` or `" wo"` trigram.

Similarity ranges from 0 (nothing in common) to 1 (identical).

## Getting started

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

## Core functions

### trgm\_similarity — continuous score

```sql theme={null}
SELECT vsql_trgm.trgm_similarity('adidas', 'adidos');   -- 0.4
SELECT vsql_trgm.trgm_similarity('cat', 'dog');          -- 0
SELECT vsql_trgm.trgm_similarity('mysql', 'mysql');      -- 1
```

All functions return NULL when any argument is NULL.

### trgm\_similar — threshold check

Returns 1 if similarity is at or above 0.3 (the PostgreSQL default), 0 otherwise:

```sql theme={null}
SELECT vsql_trgm.trgm_similar('cat', 'car');   -- 1
SELECT vsql_trgm.trgm_similar('cat', 'dog');   -- 0
```

For a custom threshold:

```sql theme={null}
SELECT vsql_trgm.trgm_similar_threshold('cat', 'car', 0.6);  -- 0
SELECT vsql_trgm.trgm_similar_threshold('cat', 'car', 0.3);  -- 1
```

### trgm\_distance — complement of similarity

`1 - similarity`. Useful for ORDER BY when you want closest first:

```sql theme={null}
SELECT vsql_trgm.trgm_distance('mysql', 'mysqk');  -- 0.5
```

## Finding near-matches in a table

The most common use: find rows that resemble a search term.

```sql theme={null}
SELECT
    name,
    vsql_trgm.trgm_similarity(name, 'adidas') AS score
FROM products
WHERE vsql_trgm.trgm_similarity(name, 'adidas') > 0.3
ORDER BY score DESC
LIMIT 10;
```

This handles typos, alternate spellings, and partial matches — cases where `LIKE '%adidas%'` would miss.

## Word similarity

`trgm_word_similarity` finds the best match between a query string and any contiguous substring of the target. This is useful when the search term might be a word inside a longer string.

```sql theme={null}
-- Find products whose name contains something close to "widget"
SELECT name, vsql_trgm.trgm_word_similarity('widget', name) AS score
FROM products
WHERE vsql_trgm.trgm_word_similar('widget', name) = 1
ORDER BY score DESC;
```

`trgm_strict_word_similarity` is a stricter variant that requires the match to align with word boundaries.

| Function | Threshold |
| - | - |
| `trgm_word_similar(a, b)` | 0.6 |
| `trgm_strict_word_similar(a, b)` | 0.5 |

## Inspecting trigrams

`trgm_show` returns the sorted trigram set for a string as a JSON array:

```sql theme={null}
SELECT vsql_trgm.trgm_show('cat');
-- '["  c"," ca","at ","cat"]'
```

This is useful for debugging why two strings score the way they do.

## Function reference

| Function | Returns | Notes |
| - | - | - |
| `trgm_similarity(a, b)` | REAL | 0–1 similarity score |
| `trgm_distance(a, b)` | REAL | 1 − similarity |
| `trgm_similar(a, b)` | INT | 1 if similarity ≥ 0.3 |
| `trgm_similar_threshold(a, b, t)` | INT | 1 if similarity ≥ t |
| `trgm_word_similarity(a, b)` | REAL | best substring match score |
| `trgm_word_distance(a, b)` | REAL | 1 − word\_similarity |
| `trgm_word_similar(a, b)` | INT | 1 if word\_similarity ≥ 0.6 |
| `trgm_strict_word_similarity(a, b)` | REAL | word-boundary-aligned score |
| `trgm_strict_word_distance(a, b)` | REAL | 1 − strict\_word\_similarity |
| `trgm_strict_word_similar(a, b)` | INT | 1 if strict\_word\_similarity ≥ 0.5 |
| `trgm_show(text)` | TEXT | JSON array of trigrams |

## Known limitations

**No index acceleration.** A preview capability for custom index types ships in the extension SDK, but the DDL that would attach one is refused on every build (`ERROR 3219: Extended Index feature not yet implemented`), so `WHERE trgm_similarity(col, 'term') > 0.3` requires a full table scan. For large tables, pre-filter with `LIKE` or full-text search to reduce the candidate set, then re-score with `trgm_similarity`.

**No session-level threshold.** PostgreSQL's `set_limit()` / `show_limit()` cannot be implemented in VEF (VEF functions are stateless). Use `trgm_similar_threshold(a, b, t)` to pass the threshold explicitly.

**No array return.** `trgm_show` returns a JSON string rather than a native array type.

## See also

* [How to implement full-text search in MySQL](https://villagesql.com/docs/en/guides/full-text-search.md)
* [How to match names phonetically and by edit distance in MySQL](https://villagesql.com/docs/en/guides/fuzzy-string-matching.md)
* [How to manipulate and transform strings in SQL](https://villagesql.com/docs/en/guides/string-functions.md)
* [How to match a pattern with LIKE in MySQL](https://villagesql.com/docs/en/tutorial/like.md)


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