Does SQL have a built-in fuzzy match function?

Standard SQL does not, but every major dialect ships something. Postgres has pg_trgm (trigram similarity) and the fuzzystrmatch extension (Soundex, Levenshtein, Metaphone). MySQL has SOUNDEX built in. SQL Server has SOUNDEX and DIFFERENCE. BigQuery has EDIT_DISTANCE and SOUNDEX. Snowflake has EDITDISTANCE, JAROWINKLER_SIMILARITY, and SOUNDEX. What you use depends on your dialect and the kind of variation you're trying to catch.

What's the difference between SOUNDEX, Levenshtein, and trigram similarity?

SOUNDEX collapses a word to a phonetic code (Robert and Rupert both become R163). It's fast but only handles English pronunciation and only compares whole words. Levenshtein (edit distance) counts the number of character insertions, deletions, and substitutions between two strings — great for typos, weak for word-reorderings. Trigram similarity breaks each string into overlapping 3-character chunks and compares the sets, which handles word reordering and partial matches better than either. In practice, a good SQL fuzzy pipeline uses trigrams (or Jaro-Winkler) for the main score and SOUNDEX as a blocking key.

How do I fuzzy match company names in Postgres?

Enable the pg_trgm extension with CREATE EXTENSION pg_trgm, then use the similarity() function or the % operator. For example: SELECT a.name, b.name, similarity(a.name, b.name) AS score FROM accounts a JOIN accounts b ON a.name % b.name WHERE a.id 0.7. Add a GIN or GiST trigram index on the name column to make this fast on tables over ~100k rows.

Is fuzzy matching in SQL slow on large tables?

A pairwise fuzzy join is O(n^2) — a million-row self-join produces a trillion comparisons and never finishes. The fix is blocking: first restrict candidate pairs with a fast exact key (SOUNDEX code, first three characters, normalized token) so you only run the expensive similarity function on plausible pairs. Postgres GIN trigram indexes, Snowflake clustering, and BigQuery partitioning all help enormously. With proper blocking, a 5M-row fuzzy dedup on Postgres runs in minutes, not days.

When should I use SQL fuzzy matching vs Python or a dedicated tool?

SQL fuzzy matching is great when the data already lives in your warehouse and you want the result to land there too — e.g., a nightly dbt model that flags duplicate accounts. It's less great when you need multi-column weighted similarity, interactive threshold tuning, or a human-in-the-loop review UI. Python (RapidFuzz/Dedupe.io) wins for custom pipelines with engineers on staff; a dedicated fuzzy tool wins for business-user workflows and one-off cleanups.

Fuzzy Matching in SQL: SOUNDEX, LEVENSHTEIN & Trigrams (Postgres, MySQL, BigQuery, Snowflake, SQL Server)

July 30, 2026 · 13 min read · Written by Sam Kale

Sooner or later every data team hits the same wall: the accounts table, the customers table, or the vendors table has duplicates that a GROUP BY can't collapse. "Acme Corp" and "ACME Corporation" are the same company; a = comparison says they're not. Two hours later you're deep in a search results page looking for "SQL fuzzy match" and finding a mess of answers written for the wrong dialect.

This guide fixes that. It's a runnable reference for fuzzy matching across the five SQL dialects most teams actually use — PostgreSQL, MySQL, SQL Server, BigQuery, and Snowflake — plus the theory you need to pick the right function and make it fast.

If you've read our Python fuzzy matching comparison, this is the SQL companion. If you haven't, no problem: everything below stands on its own.

What's in this guide

When to fuzzy match in SQL (and when not to)
The four functions you'll actually use
PostgreSQL: pg_trgm + fuzzystrmatch
MySQL: SOUNDEX and custom UDFs
SQL Server: SOUNDEX, DIFFERENCE, CLR
BigQuery: EDIT_DISTANCE and SOUNDEX
Snowflake: EDITDISTANCE and JAROWINKLER_SIMILARITY
Making it fast: blocking & indexing
A complete dedup pipeline in SQL
FAQ

When to Fuzzy Match in SQL (And When Not To)

SQL fuzzy matching is the right tool when three things are true:

The data already lives in a database or warehouse you own.
The output should land back in the same warehouse (a dbt model, a view, an audit table).
The matching logic is stable enough to run on a schedule — nightly or hourly — without a human tweaking thresholds each time.

Classic fits: nightly deduping of newly-created leads in a CDP, flagging suspected duplicate vendors in an ERP, matching incoming Stripe payments against invoices, or deduplicating a slowly-changing dimension in a warehouse.

SQL fuzzy is the wrong tool when:

You need multi-column weighted similarity with interactive tuning (SQL can do it, but the SQL gets ugly fast).
You need a review UI where a human accepts or rejects each candidate pair — use a dedicated tool.
The data is a one-off CSV that isn't in a database. Loading it just to fuzzy-match is more work than opening a fuzzy tool. See Remove Duplicates in Excel or a browser-based option like DedupFuzzy.

The Four Functions You'll Actually Use

Every SQL dialect exposes some combination of these four fuzzy-matching primitives. Understand them once, translate between dialects freely.

1. SOUNDEX

Collapses a word to a 4-character phonetic code. SOUNDEX('Robert') = 'R163', SOUNDEX('Rupert') = 'R163'. Same code → sounds similar in English.

Good for: blocking (a fast, coarse candidate filter) and matching name variants that differ in spelling but sound alike.

Bad for: anything non-English, anything longer than one word, anything where the variation is a typo in the first letter (SOUNDEX preserves the first letter verbatim).

2. Levenshtein / edit distance

The minimum number of single-character insertions, deletions, or substitutions to turn string A into string B. LEVENSHTEIN('Stripe', 'Stipe') = 1.

Good for: typos, small spelling variations, product SKU variations.

Bad for: word reordering ('John Smith' vs 'Smith, John' has a huge edit distance), abbreviations, and anything where relative length matters more than absolute.

3. Jaro-Winkler

A normalized similarity score between 0 and 1 that weights matches near the start of the string more heavily. Designed for name matching by the US Census Bureau.

Good for: person names, company names where the first token carries most of the identity.

Bad for: long free-text strings, or cases where matches at the end matter as much as at the start.

4. Trigram similarity

Break each string into overlapping 3-character chunks ('acme' → {' a', ' ac', 'acm', 'cme', 'me '}) and compute Jaccard similarity between the two sets. Score is 0-1.

Good for: almost everything. Handles word reordering, partial matches, and typos reasonably. Indexable via GIN/GiST in Postgres, which makes it the workhorse choice for large datasets.

Bad for: very short strings (a 2-character token has essentially no trigrams).

Quick comparison

Function	Output	Handles typos?	Handles reorder?	Speed
SOUNDEX	4-char code	Partial	No	Very fast
Levenshtein	Integer distance	Yes	No	Slow (O(mn))
Jaro-Winkler	Score 0-1	Yes	Weak	Fast
Trigram	Score 0-1	Yes	Yes	Fast + indexable

PostgreSQL: pg_trgm + fuzzystrmatch

Postgres has the best out-of-the-box fuzzy toolkit of any open-source database. You get two extensions:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

pg_trgm gives you trigram similarity. fuzzystrmatch gives you Soundex, Metaphone, Double Metaphone, and Levenshtein.

Trigram similarity (the workhorse)

-- similarity() returns a score 0-1
SELECT
  a.name AS name_a,
  b.name AS name_b,
  similarity(a.name, b.name) AS score
FROM accounts a
JOIN accounts b
  ON a.id < b.id
 AND a.name % b.name           -- % is the trigram similarity operator
WHERE similarity(a.name, b.name) > 0.6
ORDER BY score DESC;

The % operator is a shortcut for "similarity above the current pg_trgm.similarity_threshold", default 0.3. Tune with SET pg_trgm.similarity_threshold = 0.5.

Adding a trigram index (essential for large tables)

CREATE INDEX accounts_name_trgm_idx
  ON accounts USING GIN (name gin_trgm_ops);

With a GIN trigram index the % operator uses the index instead of scanning every row. Difference is dramatic: a self-join on a 1M-row table drops from unusable to a few minutes.

Levenshtein

SELECT levenshtein('Stripe', 'Stipe');   -- 1
SELECT levenshtein('Acme Corp', 'ACME Corporation'); -- 8

Combine with a length filter for speed: ABS(LENGTH(a) - LENGTH(b)) <= 3 as a cheap pre-filter before calling levenshtein.

Soundex & Metaphone

SELECT soundex('Robert'), soundex('Rupert');  -- both R163
SELECT metaphone('Thompson', 5);              -- TMSN
SELECT dmetaphone('Thompson');                -- TMSN

Use these as blocking keys, not as your primary similarity score.

MySQL: SOUNDEX and Custom UDFs

MySQL ships with SOUNDEX and nothing else fuzzy. Everything richer requires either a stored function or a plugin.

SOUNDEX

SELECT SOUNDEX('Robert'), SOUNDEX('Rupert');  -- both R163

SELECT a.name, b.name
FROM accounts a
JOIN accounts b
  ON SOUNDEX(a.name) = SOUNDEX(b.name)
 AND a.id < b.id;

MySQL also has SOUNDS LIKE, which is syntactic sugar: WHERE a.name SOUNDS LIKE b.name.

Levenshtein as a stored function

MySQL doesn't ship Levenshtein, but the community version is a well-established stored function. Once installed:

SELECT levenshtein('Stripe', 'Stipe');   -- 1

SELECT a.name, b.name, levenshtein(a.name, b.name) AS dist
FROM accounts a
JOIN accounts b
  ON a.id < b.id
 AND SOUNDEX(a.name) = SOUNDEX(b.name)   -- blocking!
WHERE levenshtein(a.name, b.name) <= 3;

Notice the SOUNDEX blocking step. Without it, MySQL will happily plan a full O(n²) join and never finish.

What about the reference implementation from the MySQL docs?

The classic Levenshtein UDF from the MySQL manual works but is written in C. If you can't install compiled UDFs, use the SQL-only stored function version — slower but portable.

SQL Server: SOUNDEX, DIFFERENCE, and CLR

SQL Server has SOUNDEX and a companion function DIFFERENCE that returns 0-4 based on how similar two SOUNDEX codes are.

SELECT SOUNDEX('Robert'), SOUNDEX('Rupert');      -- both R163
SELECT DIFFERENCE('Robert', 'Rupert');            -- 4 (max similarity)
SELECT DIFFERENCE('Robert', 'Xylophone');         -- 0

Fuzzy self-join with DIFFERENCE

SELECT a.name, b.name, DIFFERENCE(a.name, b.name) AS d
FROM accounts a
JOIN accounts b
  ON a.id < b.id
WHERE DIFFERENCE(a.name, b.name) = 4;

Levenshtein via CLR or T-SQL

SQL Server doesn't ship Levenshtein. Two options:

CLR assembly: compile a .NET assembly (many open-source ones exist), register it, get a proper indexed Levenshtein. Fast, but requires a DBA to enable CLR and sign the assembly.
T-SQL scalar function: works but slow. Fine for a few thousand pairs; unusable on a million-row self-join without heavy blocking.

SSIS Fuzzy Lookup / Fuzzy Grouping

If you're on SQL Server Enterprise you also get Integration Services' Fuzzy Lookup and Fuzzy Grouping transformations. They're powerful (multi-column, trainable) but SSIS-only — they don't help you write ad-hoc fuzzy joins.

BigQuery: EDIT_DISTANCE and SOUNDEX

BigQuery added first-class fuzzy functions in the string functions library.

-- Edit distance (Levenshtein)
SELECT EDIT_DISTANCE('Stripe', 'Stipe');          -- 1
SELECT EDIT_DISTANCE('Stripe', 'Stipe', max_distance => 2);

-- Soundex
SELECT SOUNDEX('Robert');                          -- R163

Fuzzy self-join

SELECT
  a.name AS name_a,
  b.name AS name_b,
  EDIT_DISTANCE(a.name, b.name) AS dist
FROM `project.dataset.accounts` a
JOIN `project.dataset.accounts` b
  ON a.id < b.id
 AND SOUNDEX(a.name) = SOUNDEX(b.name)          -- blocking
WHERE EDIT_DISTANCE(a.name, b.name, max_distance => 3) <= 3;

The max_distance argument is crucial in BigQuery — it short-circuits the computation and dramatically cuts slot time on large joins.

Trigram similarity in BigQuery

BigQuery doesn't ship trigrams natively, but you can build them with ML.NGRAMS after tokenizing, then compute Jaccard similarity with array intersection. Or use a UDF written in JavaScript. For most use cases EDIT_DISTANCE + SOUNDEX blocking is enough.

Snowflake: EDITDISTANCE and JAROWINKLER_SIMILARITY

Snowflake has the richest built-in set among the cloud warehouses.

SELECT EDITDISTANCE('Stripe', 'Stipe');                        -- 1
SELECT EDITDISTANCE('Stripe', 'Stipe', 2);                     -- with max
SELECT JAROWINKLER_SIMILARITY('Acme Corp', 'ACME Corp Inc');   -- 91
SELECT SOUNDEX('Robert');                                      -- R163

Note: JAROWINKLER_SIMILARITY in Snowflake returns 0-100 (not 0-1), which trips up people used to the Python implementation.

Fuzzy self-join

SELECT
  a.name AS name_a,
  b.name AS name_b,
  JAROWINKLER_SIMILARITY(a.name, b.name) AS score
FROM accounts a
JOIN accounts b
  ON a.id < b.id
 AND SOUNDEX(a.name) = SOUNDEX(b.name)
WHERE JAROWINKLER_SIMILARITY(a.name, b.name) >= 85
ORDER BY score DESC;

Snowflake compensates for the missing trigram operator with sheer horsepower — the cost is compute credits rather than latency. A properly blocked fuzzy self-join on a 10M-row table finishes in minutes on a Medium warehouse.

Making It Fast: Blocking & Indexing

The single most important concept in SQL fuzzy matching is blocking. A raw fuzzy self-join is O(n²). At 1M rows that's 1 trillion pairs. Nobody has time.

Blocking means restricting the join to plausible candidate pairs using a fast exact key, so the expensive similarity function runs on a small subset.

Common blocking keys

Key	Catches	Misses
SOUNDEX of first token	Phonetic variants	First-letter typos
First 3 characters (lowercased)	Suffix/legal-form variants	Prefix typos
Normalized email domain	Same-company duplicates	Free-mail duplicates
Postal code / city	Address duplicates	Moved-address duplicates
Trigram overlap (Postgres `%`)	Nearly everything	Very short strings

In production it's normal to run multiple blocking passes with UNION ALL, each catching a different variation, then de-dupe the resulting candidate pair set. This is standard practice in libraries like dedupe.io; you can implement it directly in SQL.

Index cheat-sheet

Postgres: GIN or GiST trigram index on the fuzzy column. GIN is faster for reads, slower for writes.
MySQL: Regular B-tree on SOUNDEX(name) if you're on 5.7+ (generated column + index).
SQL Server: Persisted computed column on SOUNDEX(name) + non-clustered index.
BigQuery: Cluster the table by SOUNDEX(name) or the first 3 characters of the normalized name.
Snowflake: Cluster on SOUNDEX(name) or use a materialized view with the SOUNDEX code precomputed.

A Complete Dedup Pipeline in SQL

Putting it together: here's a full dedup pipeline in Postgres. Adapt the syntax and you have the same in the other four dialects.

-- 1. Normalize into a staging view
CREATE OR REPLACE VIEW accounts_norm AS
SELECT
  id,
  name,
  lower(regexp_replace(
    regexp_replace(name, '\s+(Inc|LLC|Ltd|Corp|GmbH|Pvt Ltd)\.?$', '', 'i'),
    '\s+', ' ', 'g'
  )) AS name_norm,
  soundex(split_part(name, ' ', 1)) AS block_key
FROM accounts;

-- 2. Generate candidate pairs via SOUNDEX blocking
CREATE TABLE candidate_pairs AS
SELECT
  a.id AS id_a,
  b.id AS id_b,
  a.name_norm AS name_a,
  b.name_norm AS name_b,
  similarity(a.name_norm, b.name_norm) AS score
FROM accounts_norm a
JOIN accounts_norm b
  ON a.block_key = b.block_key
 AND a.id < b.id
WHERE similarity(a.name_norm, b.name_norm) > 0.7;

-- 3. Auto-merge above threshold, flag the review band
CREATE TABLE merge_actions AS
SELECT
  id_a,
  id_b,
  score,
  CASE
    WHEN score >= 0.90 THEN 'auto_merge'
    WHEN score >= 0.75 THEN 'review'
    ELSE 'ignore'
  END AS action
FROM candidate_pairs;

-- 4. Pick survivors (earliest created_at wins)
CREATE TABLE survivors AS
SELECT
  LEAST(a.id, b.id) AS survivor_id,
  GREATEST(a.id, b.id) AS loser_id,
  ma.score
FROM merge_actions ma
JOIN accounts a ON a.id = ma.id_a
JOIN accounts b ON b.id = ma.id_b
WHERE ma.action = 'auto_merge'
  AND a.created_at <= b.created_at;

Wire this into dbt as four models (accounts_norm, candidate_pairs, merge_actions, survivors), schedule nightly, and you have a dedup pipeline your team can inspect, audit, and version.

Sanity check before you auto-merge in production: hold the first run's auto_merge output for manual review anyway. Sample 50 pairs by eye. If more than 1 is a false positive, your threshold is too loose — raise it and rerun. See the thresholds section of our dedup guide for the reasoning.

FAQ

Which SQL dialect is best for fuzzy matching?

Postgres, by a wide margin, thanks to pg_trgm and its GIN trigram indexes. Snowflake is second because of built-in Jaro-Winkler. BigQuery has decent primitives but no trigram support. SQL Server and MySQL require the most legwork.

How do I fuzzy match multiple columns in SQL?

Compute per-column scores, then combine with weights. Example: 0.5 * similarity(a.name, b.name) + 0.3 * similarity(a.email, b.email) + 0.2 * similarity(a.city, b.city). Filter the result on the combined score. Getting the weights right is more art than science; see Fuzzy Match on Multiple Columns.

Can I fuzzy match across two different tables (not a self-join)?

Yes, same functions, just FROM table_a a JOIN table_b b ON ... instead of a self-join. The blocking rule still applies — the join needs a fast exact key or the query will time out.

Do fuzzy functions work with non-English characters?

Levenshtein and trigrams handle Unicode fine (they operate on code points). SOUNDEX is English-only. For international name matching, use trigrams or Jaro-Winkler; ignore SOUNDEX.

How do I benchmark fuzzy matching performance?

Time a single query with EXPLAIN ANALYZE (Postgres) or QUERY PROFILE (Snowflake, BigQuery). The two knobs that matter: number of candidate pairs after blocking, and cost per pair (dominated by string length in Levenshtein, by trigram count in trigram similarity). Halving either one halves runtime.

Is there a fuzzy matching library that works across all these dialects?

Not really. dbt-utils has some string helpers but no fuzzy dedup macros. Splink is a great open-source library that generates SQL for several backends (Spark, DuckDB, Postgres, Athena) but not Snowflake or BigQuery natively. In practice, teams write dialect-specific SQL and abstract it in dbt models.

Wrapping Up

Fuzzy matching in SQL is a superpower once you know which functions your dialect ships and how to use blocking to keep the joins tractable. The pipeline is always the same: normalize, block, score, threshold, pick survivors, log. The syntax differs.

For warehouse-native dedup pipelines that need to run on a schedule and land results back in the warehouse, SQL is the right home. For one-off cleanups, browser-based tools like DedupFuzzy are usually faster. For interactive multi-column tuning with a human reviewer, use a dedicated tool. Pick the right tool for the shape of the problem and you'll spend a lot less time yak-shaving in Stack Overflow.

Fuzzy Matching in SQL: SOUNDEX, LEVENSHTEIN & Trigrams (Postgres, MySQL, BigQuery, Snowflake, SQL Server)

What's in this guide

When to Fuzzy Match in SQL (And When Not To)

The Four Functions You'll Actually Use

1. SOUNDEX

2. Levenshtein / edit distance

3. Jaro-Winkler

4. Trigram similarity

Quick comparison

PostgreSQL: pg_trgm + fuzzystrmatch

Trigram similarity (the workhorse)

Adding a trigram index (essential for large tables)

Levenshtein

Soundex & Metaphone

MySQL: SOUNDEX and Custom UDFs

SOUNDEX

Levenshtein as a stored function

What about the reference implementation from the MySQL docs?

SQL Server: SOUNDEX, DIFFERENCE, and CLR

Fuzzy self-join with DIFFERENCE

Levenshtein via CLR or T-SQL

SSIS Fuzzy Lookup / Fuzzy Grouping

BigQuery: EDIT_DISTANCE and SOUNDEX

Fuzzy self-join

Trigram similarity in BigQuery

Snowflake: EDITDISTANCE and JAROWINKLER_SIMILARITY

Fuzzy self-join

Making It Fast: Blocking & Indexing

Common blocking keys

Index cheat-sheet

A Complete Dedup Pipeline in SQL

FAQ

Which SQL dialect is best for fuzzy matching?

How do I fuzzy match multiple columns in SQL?

Can I fuzzy match across two different tables (not a self-join)?

Do fuzzy functions work with non-English characters?

How do I benchmark fuzzy matching performance?

Is there a fuzzy matching library that works across all these dialects?

Wrapping Up

Related Reading