DuckDB Extension
RapidFuzz
Compare strings and rank candidate matches in DuckDB SQL using RapidFuzz. Find similar names, pick the best match from a list, match reordered words, or calculate edit distances.
On this page
Sample data for the examples
Each browser example starts with these sample tables; sample files are shared across examples on this page. Run this setup first when trying the examples in your own DuckDB session.
-- Fixtures for rapidfuzz cookbook / technical-details / quickStart snippets.
-- Small, deterministic, idempotent. Tables seeded with a few near-duplicates
-- so blocking, dedupe, top-1, and spell-check recipes return non-empty results.
CREATE OR REPLACE TABLE customers (
id INTEGER,
name VARCHAR,
address VARCHAR
);
INSERT INTO customers VALUES
(1, 'Acme Corporation', '123 Main Street'),
(2, 'ACME Corp.', '123 Main St'),
(3, 'Acme Corp', '123 Main St.'),
(4, 'Globex Industries', '500 Innovation Way'),
(5, 'Globex Industires', '500 Innovation Way'),
(6, 'Initech', '742 Evergreen Terrace'),
(7, 'Initech LLC', '742 Evergreen Ter.'),
(8, 'Soylent Green Foods', '1 Future Plaza'),
(9, 'Soylent Green Foods Inc', '1 Future Plaza Suite 2');
CREATE OR REPLACE TABLE left_records (
id INTEGER,
name VARCHAR
);
INSERT INTO left_records VALUES
(1, 'Jonathan Smith'),
(2, 'Catherine Jones'),
(3, 'Robert Williams'),
(4, 'Elizabeth Brown');
CREATE OR REPLACE TABLE right_records (
id INTEGER,
name VARCHAR
);
INSERT INTO right_records VALUES
(10, 'Jonathon Smith'),
(11, 'Catharine Jones'),
(12, 'Robert Willams'),
(13, 'Elisabeth Browne'),
(14, 'Michael Davis');
CREATE OR REPLACE TABLE titles (
id INTEGER,
title VARCHAR
);
INSERT INTO titles VALUES
(1, 'The Quick Brown Fox'),
(2, 'Quick Brown Fox The'),
(3, 'the quick brown fox jumps'),
(4, 'Lazy Dog Sleeping'),
(5, 'Sleeping Lazy Dog'),
(6, 'Hello World Example'),
(7, 'Example Hello World');
CREATE OR REPLACE TABLE dictionary (
word VARCHAR
);
INSERT INTO dictionary VALUES
('apple'), ('apply'), ('ample'), ('amplify'),
('banana'), ('bandana'), ('bandage'),
('orange'), ('arrange'), ('range'),
('grape'), ('grade'), ('great');
Find candidate matches when text differs
Exact equality misses names with typing errors, changed word order, or small spelling differences. RapidFuzz adds similarity scores and edit distances to DuckDB so you can compare values and rank possible matches in SQL.
Use it for record linkage, reviewing possible duplicates, or matching input against a known list. A high score is evidence for a candidate, not proof of identity. For large tables, narrow the candidate pairs with a rule such as a shared postcode before scoring them.
Example
Find the closest name for each record
Compare two input names with three candidates. Keep the highest-scoring candidate with a score of at least 80 for each input, using the candidate id to break ties.
Tested with DuckDB 2.0.0. Browser examples use Haybarn-Wasm 2.0.0-rc12.
SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
WITH PREFIX 'https://duckdb-extensions.query.farm';
INSTALL rapidfuzz FROM query_farm;
LOAD rapidfuzz FROM query_farm;
WITH names(id, name) AS (
VALUES (1, 'Jon Smith'), (2, 'Jane Doe')
), candidates(id, name) AS (
VALUES (10, 'John Smith'), (11, 'Jane Doe'), (12, 'Jon Smyth')
)
SELECT a.name AS input_name, b.name AS matched_name,
round(rapidfuzz_ratio(a.name, b.name), 1) AS score
FROM names a CROSS JOIN candidates b
WHERE rapidfuzz_ratio(a.name, b.name) >= 80
QUALIFY row_number() OVER (
PARTITION BY a.id
ORDER BY rapidfuzz_ratio(a.name, b.name) DESC, b.id
) = 1
ORDER BY a.id;
SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
WITH PREFIX 'https://haybarn-extensions.query.farm';
INSTALL rapidfuzz FROM query_farm;
LOAD rapidfuzz FROM query_farm;
WITH names(id, name) AS (
VALUES (1, 'Jon Smith'), (2, 'Jane Doe')
), candidates(id, name) AS (
VALUES (10, 'John Smith'), (11, 'Jane Doe'), (12, 'Jon Smyth')
)
SELECT a.name AS input_name, b.name AS matched_name,
round(rapidfuzz_ratio(a.name, b.name), 1) AS score
FROM names a CROSS JOIN candidates b
WHERE rapidfuzz_ratio(a.name, b.name) >= 80
QUALIFY row_number() OVER (
PARTITION BY a.id
ORDER BY rapidfuzz_ratio(a.name, b.name) DESC, b.id
) = 1
ORDER BY a.id;
INSTALL rapidfuzz FROM community;
LOAD rapidfuzz;
WITH names(id, name) AS (
VALUES (1, 'Jon Smith'), (2, 'Jane Doe')
), candidates(id, name) AS (
VALUES (10, 'John Smith'), (11, 'Jane Doe'), (12, 'Jon Smyth')
)
SELECT a.name AS input_name, b.name AS matched_name,
round(rapidfuzz_ratio(a.name, b.name), 1) AS score
FROM names a CROSS JOIN candidates b
WHERE rapidfuzz_ratio(a.name, b.name) >= 80
QUALIFY row_number() OVER (
PARTITION BY a.id
ORDER BY rapidfuzz_ratio(a.name, b.name) DESC, b.id
) = 1
ORDER BY a.id;
Expected result
| input_name | matched_name | score |
|---|---|---|
| Jon Smith | John Smith | 94.7 |
| Jane Doe | Jane Doe | 100 |
A similarity score identifies a candidate, not a confirmed duplicate. This tiny example compares every pair. For larger tables, reduce the candidate pairs before scoring. Inputs with no score of at least 80 are omitted.
Example
Pick the best match from a list
Map each customer name to the closest of three canonical company names, ignoring word order and repeated words.
Tested with DuckDB 2.0.0. Run this example in the DuckDB CLI or a native client.
SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
WITH PREFIX 'https://duckdb-extensions.query.farm';
INSTALL rapidfuzz FROM query_farm;
LOAD rapidfuzz FROM query_farm;
SELECT name,
rapidfuzz_extract_one(name, ['Acme Corporation', 'Globex Industries', 'Initech'],
scorer := 'token_set_ratio').choice AS canonical
FROM (VALUES ('ACME Corp.'), ('Globex Industires'), ('Initech LLC')) t(name);
SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
WITH PREFIX 'https://haybarn-extensions.query.farm';
INSTALL rapidfuzz FROM query_farm;
LOAD rapidfuzz FROM query_farm;
SELECT name,
rapidfuzz_extract_one(name, ['Acme Corporation', 'Globex Industries', 'Initech'],
scorer := 'token_set_ratio').choice AS canonical
FROM (VALUES ('ACME Corp.'), ('Globex Industires'), ('Initech LLC')) t(name);
INSTALL rapidfuzz FROM community;
LOAD rapidfuzz;
SELECT name,
rapidfuzz_extract_one(name, ['Acme Corporation', 'Globex Industries', 'Initech'],
scorer := 'token_set_ratio').choice AS canonical
FROM (VALUES ('ACME Corp.'), ('Globex Industires'), ('Initech LLC')) t(name);
Expected result
| name | canonical |
|---|---|
| ACME Corp. | Acme Corporation |
| Globex Industires | Globex Industries |
| Initech LLC | Initech |
Common tasks
- Choose a similarity function Compare ratio, token, and edit-distance functions.
- Pick the best match from a list Rank choices for each value with one function call.
- Reduce the number of comparisons Use blocking rules before joining large datasets.
- Normalize names before matching Apply consistent casing and punctuation rules.
- Find possible duplicate records Compare records within a table and review the candidate pairs.
Deep Dive
Technical Details
Comparing records
A similarity score helps identify records to review. It does not establish that two records refer to the same person or company. Choose a threshold using examples from your own data, and check both false matches and missed matches.
Architecture
This extension binds the RapidFuzz C++ library β the same engine behind Pythonβs rapidfuzz package and the spiritual successor to fuzzywuzzy β as DuckDB scalar functions. There is no extra state, no secrets, no ATTACH interface; calls are pure functions composable into any SELECT, WHERE, ON, or ORDER BY clause.
Four function families coexist:
- Ratio scores (
rapidfuzz_ratio,rapidfuzz_wratioand friends) return aDOUBLEfrom 0 to 100, mirroring the Pythonrapidfuzz.fuzzfamily. - Metrics (Levenshtein, Damerau-Levenshtein, OSA, Indel, LCS, Hamming, Jaro, Jaro-Winkler, Prefix, Postfix) ship four variants each β
_distance,_similarity,_normalized_distance,_normalized_similarity. The naming is mechanical:rapidfuzz_<algo>[_normalized]_<distance|similarity>. - Best match β
rapidfuzz_extractandrapidfuzz_extract_onerank a list of choices, like Pythonβsrapidfuzz.process. - Alignment β the edit operations and matching ranges behind a score:
rapidfuzz_levenshtein_editops,rapidfuzz_levenshtein_opcodes, their Indel counterparts, andrapidfuzz_partial_ratio_alignment.
Behavior matches the upstream library closely enough that the RapidFuzz documentation is the authoritative reference for algorithm semantics.
Characters, not bytes
Strings are compared by Unicode character. An accented letter or a CJK character is one character, so scores, distances and alignment positions agree with Pythonβs rapidfuzz:
SELECT rapidfuzz_levenshtein_distance('cafΓ©', 'cafe') AS rapidfuzz, levenshtein('cafΓ©', 'cafe') AS builtin_bytes;DuckDBβs built-in levenshtein compares bytes and reports 2 here. Pure ASCII strings take a byte-level fast path.
Return types
Raw _distance and _similarity of the counting metrics (Levenshtein, Damerau-Levenshtein, OSA, Indel, LCS, Hamming, Prefix, Postfix) return BIGINT. Normalized variants, Jaro, Jaro-Winkler and the ratio scores return DOUBLE. A NULL string gives NULL.
Options
Options are passed by name with := and must be constants:
| Option | Functions | Default |
|---|---|---|
score_cutoff |
every scorer, rapidfuzz_extract, rapidfuzz_extract_one |
NULL (no cutoff) |
insert_cost, delete_cost, replace_cost |
rapidfuzz_levenshtein_* |
1 |
pad |
rapidfuzz_hamming_* |
true |
prefix_weight |
rapidfuzz_jaro_winkler_* (0 to 0.25) |
0.1 |
scorer, top_k |
rapidfuzz_extract (scorer also on rapidfuzz_extract_one) |
'wratio', 5 |
score_cutoff follows RapidFuzz: distances above the cutoff come back as cutoff + 1 (1.0 when normalized) and similarities below it as 0. Besides making the threshold explicit, it lets RapidFuzz abandon a comparison as soon as it canβt reach the cutoff:
SELECT nameFROM customersWHERE rapidfuzz_levenshtein_distance(name, 'Acme Corp', score_cutoff := 3) <= 3;Changes in the DuckDB 2.0 release
The DuckDB 2.0 build of the extension compares Unicode characters instead of bytes, returns BIGINT for the counting metrics (previously DOUBLE), and adds Levenshtein, Damerau-Levenshtein, rapidfuzz_wratio, rapidfuzz_qratio, the token_ratio variants, best-match extraction, alignment functions and the options above. Results for ASCII strings are unchanged. The changelog lists every change.
Pick the right algorithm
| Situation | Function |
|---|---|
| One score, 0β100, sane default | rapidfuzz_ratio |
| Donβt know how the strings differ | rapidfuzz_wratio |
| Best match from a list of choices | rapidfuzz_extract_one |
| One string is a fragment of the other | rapidfuzz_partial_ratio |
| Same words, different order | rapidfuzz_token_sort_ratio |
| Duplicated or stray words | rapidfuzz_token_set_ratio |
| Human names, typos at the end | rapidfuzz_jaro_winkler_normalized_similarity |
| Single-character typos / adjacent swaps | rapidfuzz_osa_distance |
| Edit distance with custom costs | rapidfuzz_levenshtein_distance |
| Swaps that are also edited further | rapidfuzz_damerau_levenshtein_distance |
| Equal-length identifiers (SKUs, hashes) | rapidfuzz_hamming_distance |
For cross-pair comparisons where strings differ in length, prefer the _normalized_* variants β raw distance penalizes longer strings just for being long. See the Wikipedia article on JaroβWinkler distance for the formal definitions of the name-matching variants.
Blocking strategies
A cross join of two tables with 100,000 rows produces 10 billion candidate pairs. Restrict those pairs before scoring. Each restriction trades fewer comparisons for a risk of missing a match:
Length bucket. Two strings that differ in length by more than k characters canβt have a Levenshtein distance β€ k. Filter on length(a) BETWEEN length(b) - k AND length(b) + k.
First-letter (or first-N) prefix. Cheap join key. Misses pairs where the first letter is itself the typo (rare for names, common for OCRβd text).
Length + first-letter combined. Combine both rules when they fit your data. Measure the remaining candidate count before running the full join.
Phonetic prefix (soundex / metaphone). When typos are phonetic (βSmythβ vs βSmithβ). DuckDBβs built-in fuzzy extension supplies the codes; group by code and only score within a group.
Token-based blocking. For multi-word strings, require at least one token in common (e.g. via a normalized array intersection). Excellent for company names where the legal suffix differs but the brand word is shared.
Put the candidate restrictions in the join condition and inspect the query plan when performance matters.
Pre-normalize once, score many
Hoist deterministic transformations out of the join so they run O(n) instead of O(nΒ²):
WITH normalized AS ( SELECT id, regexp_replace(lower(name), '[^a-z0-9 ]', '', 'g') AS name_n, length(name) AS len, left(lower(name), 1) AS block_letter FROM customers)SELECT a.id, b.id, rapidfuzz_token_set_ratio(a.name_n, b.name_n) AS scoreFROM normalized aJOIN normalized b ON a.id < b.id AND a.block_letter = b.block_letter AND a.len BETWEEN b.len - 3 AND b.len + 3WHERE rapidfuzz_token_set_ratio(a.name_n, b.name_n) > 90;When you also need consistent casing, pluralization, or struct-key rewrites, the sibling inflector extension covers the cleanup side of the same workflow β both sit in the matching-and-casing string-munging bucket.
Top-1 per left row
Record-linkage usually wants exactly one best match per left record. Use QUALIFY (DuckDBβs window-filter clause):
SELECT a.id AS left_id, b.id AS right_id, rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) AS scoreFROM left_records aJOIN right_records b ON left(a.name, 1) = left(b.name, 1) -- blockingWHERE rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) > 0.92QUALIFY ROW_NUMBER() OVER ( PARTITION BY a.id ORDER BY rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) DESC, b.id) = 1;Best match from a list
When the candidates fit in a list β a few hundred canonical names, a product catalogβs categories β rapidfuzz_extract_one returns the best one per row without a join or a window function. The query string is preprocessed once and compared with every choice:
SELECT a.id, a.name, rapidfuzz_extract_one(a.name, c.names, scorer := 'jaro_winkler_normalized_similarity', score_cutoff := 0.9) AS bestFROM left_records a, (SELECT list(name ORDER BY id) AS names FROM right_records) cORDER BY a.id;The result is a struct with choice, score and the 1-based choice_index, or NULL when nothing reaches the cutoff. rapidfuzz_extract returns the top_k best matches as a list instead.
Performance
- Compare against a constant when you can. When one argument is a constant, it is preprocessed once and reused for every row β about twice as fast as comparing two columns.
- The scorer matters more than anything else.
rapidfuzz_ratioand the metrics are cheap;rapidfuzz_partial_ratioandrapidfuzz_wratiotry many alignments and are an order of magnitude slower. Passscore_cutoffso they can skip hopeless pairs. - Small tables use few threads. DuckDB parallelizes a scan in row groups of about 122,880 rows, so an expensive scorer over a table of a few hundred thousand rows only uses a few cores. Large tables and joins parallelize normally.
When to reach for something else
- Trigram indexes / FTS. For a single query string against millions of rows, a trigram or full-text index returns top-N candidates in sub-linear time. Score the candidates with RapidFuzz, but donβt expect this extension to replace the index.
- Embeddings / vector search. If βsimilarityβ really means semantic similarity (paraphrase, translation), edit distance is the wrong tool. Reach for an embedding model and a vector index instead.
- Production fuzzy joins on >10M rows. Even with aggressive blocking, very large self-joins benefit from purpose-built record-linkage tooling (Splink, Dedupe). RapidFuzz is the right primitive for the inner-loop score; a record-linkage framework manages the candidate generation, blocking, and resolution around it.
Reference
Extension Contents
Quick reference to all available functions and settings organized by category.
| Name | Type | Description |
|---|---|---|
|
Alignment
Where two strings differ: the edit operations, the equal/changed blocks covering both strings, and where |
||
| rapidfuzz_indel_editops() | Object type: Scalar function |
The Indel edit operations ('insert' or 'delete', no substitutions) that turn s1 into s2, as structs with operation, s1_pos and s2_pos.
|
| rapidfuzz_indel_opcodes() | Object type: Scalar function | Describes how to turn s1 into s2 as 'equal', 'insert' and 'delete' blocks covering both strings, based on Indel edits. |
| rapidfuzz_levenshtein_editops() | Object type: Scalar function |
The Levenshtein edit operations that turn s1 into s2: a list of structs with the operation ('insert', 'delete' or 'replace') and the positions s1_pos and s2_pos it applies at.
|
| rapidfuzz_levenshtein_opcodes() | Object type: Scalar function |
Describes how to turn s1 into s2 as blocks that cover both strings: each struct has an operation ('equal', 'insert', 'delete' or 'replace') and the ranges s1_startβs1_end and s2_startβs2_end.
|
| rapidfuzz_partial_ratio_alignment() | Object type: Scalar function |
The rapidfuzz_partial_ratio score together with where it was found: a struct with score and the ranges s1_startβs1_end and s2_startβs2_end of the best-matching substrings.
|
|
Best Match
Pick the best matches for a string from a list of choices, like Python's |
||
| rapidfuzz_extract() | Object type: Scalar function |
The best matches for query among choices, best first, like Python's rapidfuzz.process.extract.
|
| rapidfuzz_extract_one() | Object type: Scalar function |
The single best match for query among choices, like Python's rapidfuzz.process.extractOne: a struct with the choice, its score and its 1-based choice_index, or NULL when no choice reaches score_cutoff.
|
|
Damerau-Levenshtein
Levenshtein plus swaps of two characters as a single edit, even when other edits happen between them. |
||
| rapidfuzz_damerau_levenshtein_distance() | Object type: Scalar function | Damerau-Levenshtein distance: like Levenshtein, but swapping two characters counts as one edit, even when other edits happen between them. |
| rapidfuzz_damerau_levenshtein_normalized_distance() | Object type: Scalar function | Damerau-Levenshtein distance: like Levenshtein, but swapping two characters counts as one edit, even when other edits happen between them. |
| rapidfuzz_damerau_levenshtein_normalized_similarity() | Object type: Scalar function | Damerau-Levenshtein distance: like Levenshtein, but swapping two characters counts as one edit, even when other edits happen between them. |
| rapidfuzz_damerau_levenshtein_similarity() | Object type: Scalar function | Damerau-Levenshtein distance: like Levenshtein, but swapping two characters counts as one edit, even when other edits happen between them. |
|
Hamming
Position-by-position character match count. Meant for strings of the same length; a shorter input is padded, so each extra character in the longer one counts as a difference. |
||
| rapidfuzz_hamming_distance() | Object type: Scalar function | Hamming distance: the number of positions at which the characters differ. |
| rapidfuzz_hamming_normalized_distance() | Object type: Scalar function | Hamming distance: the number of positions at which the characters differ. |
| rapidfuzz_hamming_normalized_similarity() | Object type: Scalar function | Hamming distance: the number of positions at which the characters differ. |
| rapidfuzz_hamming_similarity() | Object type: Scalar function | Hamming distance: the number of positions at which the characters differ. |
|
Indel
Insertion-and-deletion edit distance β no substitutions counted. |
||
| rapidfuzz_indel_distance() | Object type: Scalar function | Indel distance: the minimum number of insertions and deletions (no substitutions) that turn s1 into s2. |
| rapidfuzz_indel_normalized_distance() | Object type: Scalar function | Indel distance: the minimum number of insertions and deletions (no substitutions) that turn s1 into s2. |
| rapidfuzz_indel_normalized_similarity() | Object type: Scalar function | Indel distance: the minimum number of insertions and deletions (no substitutions) that turn s1 into s2. |
| rapidfuzz_indel_similarity() | Object type: Scalar function | Indel distance: the minimum number of insertions and deletions (no substitutions) that turn s1 into s2. |
|
Jaro
Jaro similarity β match-and-transposition based. Good for short strings and typos. |
||
| rapidfuzz_jaro_distance() | Object type: Scalar function | Jaro similarity, based on matching characters and transpositions. |
| rapidfuzz_jaro_normalized_distance() | Object type: Scalar function | Jaro similarity, based on matching characters and transpositions. |
| rapidfuzz_jaro_normalized_similarity() | Object type: Scalar function | Jaro similarity, based on matching characters and transpositions. |
| rapidfuzz_jaro_similarity() | Object type: Scalar function | Jaro similarity, based on matching characters and transpositions. |
|
Jaro Winkler
Jaro-Winkler β Jaro with extra weight on a shared prefix. Strong default for human names. |
||
| rapidfuzz_jaro_winkler_distance() | Object type: Scalar function | Jaro-Winkler similarity: Jaro with extra weight for a shared prefix of up to four characters. |
| rapidfuzz_jaro_winkler_normalized_distance() | Object type: Scalar function | Jaro-Winkler similarity: Jaro with extra weight for a shared prefix of up to four characters. |
| rapidfuzz_jaro_winkler_normalized_similarity() | Object type: Scalar function | Jaro-Winkler similarity: Jaro with extra weight for a shared prefix of up to four characters. |
| rapidfuzz_jaro_winkler_similarity() | Object type: Scalar function | Jaro-Winkler similarity: Jaro with extra weight for a shared prefix of up to four characters. |
|
Lcs Seq
Longest common subsequence β order-preserving but not adjacency-preserving. |
||
| rapidfuzz_lcs_seq_distance() | Object type: Scalar function | Longest common subsequence: the characters both strings share in the same order, not necessarily adjacent. |
| rapidfuzz_lcs_seq_normalized_distance() | Object type: Scalar function | Longest common subsequence: the characters both strings share in the same order, not necessarily adjacent. |
| rapidfuzz_lcs_seq_normalized_similarity() | Object type: Scalar function | Longest common subsequence: the characters both strings share in the same order, not necessarily adjacent. |
| rapidfuzz_lcs_seq_similarity() | Object type: Scalar function | Longest common subsequence: the characters both strings share in the same order, not necessarily adjacent. |
|
Levenshtein
Insertions, deletions and substitutions, each with a configurable cost. Counts characters, unlike DuckDB's built-in byte-based |
||
| rapidfuzz_levenshtein_distance() | Object type: Scalar function | Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. |
| rapidfuzz_levenshtein_normalized_distance() | Object type: Scalar function | Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. |
| rapidfuzz_levenshtein_normalized_similarity() | Object type: Scalar function | Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. |
| rapidfuzz_levenshtein_similarity() | Object type: Scalar function | Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. |
|
Osa
Optimal String Alignment (a.k.a. restricted Damerau-Levenshtein) β counts adjacent transpositions as a single edit. |
||
| rapidfuzz_osa_distance() | Object type: Scalar function | Optimal String Alignment (restricted Damerau-Levenshtein) distance: Levenshtein plus swaps of two adjacent characters as a single edit, with no further edits to a swapped pair. |
| rapidfuzz_osa_normalized_distance() | Object type: Scalar function | Optimal String Alignment (restricted Damerau-Levenshtein) distance: Levenshtein plus swaps of two adjacent characters as a single edit, with no further edits to a swapped pair. |
| rapidfuzz_osa_normalized_similarity() | Object type: Scalar function | Optimal String Alignment (restricted Damerau-Levenshtein) distance: Levenshtein plus swaps of two adjacent characters as a single edit, with no further edits to a swapped pair. |
| rapidfuzz_osa_similarity() | Object type: Scalar function | Optimal String Alignment (restricted Damerau-Levenshtein) distance: Levenshtein plus swaps of two adjacent characters as a single edit, with no further edits to a swapped pair. |
|
Postfix
Edit distance / similarity considering only the matching suffix portion. |
||
| rapidfuzz_postfix_distance() | Object type: Scalar function | Postfix similarity: the length of the common suffix of both strings. |
| rapidfuzz_postfix_normalized_distance() | Object type: Scalar function | Postfix similarity: the length of the common suffix of both strings. |
| rapidfuzz_postfix_normalized_similarity() | Object type: Scalar function | Postfix similarity: the length of the common suffix of both strings. |
| rapidfuzz_postfix_similarity() | Object type: Scalar function | Postfix similarity: the length of the common suffix of both strings. |
|
Prefix
Edit distance / similarity considering only the matching prefix portion of the strings. |
||
| rapidfuzz_prefix_distance() | Object type: Scalar function | Prefix similarity: the length of the common prefix of both strings. |
| rapidfuzz_prefix_normalized_distance() | Object type: Scalar function | Prefix similarity: the length of the common prefix of both strings. |
| rapidfuzz_prefix_normalized_similarity() | Object type: Scalar function | Prefix similarity: the length of the common prefix of both strings. |
| rapidfuzz_prefix_similarity() | Object type: Scalar function | Prefix similarity: the length of the common prefix of both strings. |
|
Ratio Scores
0β100 similarity scores matching the Python |
||
| rapidfuzz_partial_ratio() | Object type: Scalar function |
The best rapidfuzz_ratio of the shorter string against any equally long substring of the longer one.
|
| rapidfuzz_partial_token_ratio() | Object type: Scalar function |
The higher of rapidfuzz_partial_token_sort_ratio and rapidfuzz_partial_token_set_ratio, computed in one pass.
|
| rapidfuzz_partial_token_set_ratio() | Object type: Scalar function |
rapidfuzz_partial_ratio applied to the word sets of both strings.
|
| rapidfuzz_partial_token_sort_ratio() | Object type: Scalar function |
rapidfuzz_partial_ratio after sorting the words of both strings: word order is ignored and the shorter string may match part of the longer one.
|
| rapidfuzz_qratio() | Object type: Scalar function |
Quick ratio, the same as Python's rapidfuzz.fuzz.QRatio: rapidfuzz_ratio, except that it returns 0 when either string is empty.
|
| rapidfuzz_ratio() | Object type: Scalar function |
Normalized Indel similarity scaled to 0β100, the same as Python's rapidfuzz.fuzz.ratio.
|
| rapidfuzz_token_ratio() | Object type: Scalar function |
The higher of rapidfuzz_token_sort_ratio and rapidfuzz_token_set_ratio, computed in one pass, which is faster than calling both.
|
| rapidfuzz_token_set_ratio() | Object type: Scalar function | Compares the sets of words in both strings, ignoring word order and repeated words. |
| rapidfuzz_token_sort_ratio() | Object type: Scalar function |
rapidfuzz_ratio after splitting both strings on whitespace and sorting the words, so word order is ignored.
|
| rapidfuzz_wratio() | Object type: Scalar function |
Weighted ratio, the same as Python's rapidfuzz.fuzz.WRatio: combines rapidfuzz_ratio with the partial and token scorers, weighting them by how different the string lengths are.
|
No extension contents match that search.
API Reference
Function Reference
Practical Examples
Cookbook
Real-world recipes and patterns for common use cases.
Quick reference
| Need | Pick |
|---|---|
| One score, 0β100, sane default | rapidfuzz_ratio |
| Donβt know how the strings differ | rapidfuzz_wratio |
| Best match from a list | rapidfuzz_extract_one |
| Match a fragment inside a longer string | rapidfuzz_partial_ratio |
| Words in different orders | rapidfuzz_token_sort_ratio |
| Tolerate duplicated words | rapidfuzz_token_set_ratio |
| Names / typos | rapidfuzz_jaro_winkler_normalized_similarity |
| Spelling distance with transpositions | rapidfuzz_osa_distance |
| Strings of identical length | rapidfuzz_hamming_distance |
| Where two strings differ | rapidfuzz_levenshtein_opcodes |
Record linkage
Find the best match for each row in left against right, scored by Jaro-Winkler:
SELECT a.id AS left_id, b.id AS right_id, rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) AS simFROM left_records a, right_records bWHERE rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) > 0.92ORDER BY a.id, sim DESC;For the single best match per left row, wrap with QUALIFY:
SELECT *FROM ( SELECT a.id AS left_id, b.id AS right_id, rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) AS sim, ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) DESC) AS rn FROM left_records a, right_records b)WHERE rn = 1 AND sim > 0.92;Dedupe
Cluster rows by similarity threshold:
WITH pairs AS ( SELECT a.id AS l, b.id AS r, rapidfuzz_token_set_ratio(a.title, b.title) AS sim FROM titles a, titles b WHERE a.id < b.id AND rapidfuzz_token_set_ratio(a.title, b.title) > 90)SELECT * FROM pairs ORDER BY sim DESC;Use the pair list to assign cluster IDs (graph-walk in Python or with a recursive CTE).
Canonicalize against a known list
Map messy values onto a short list of canonical ones in a single pass β no join, no window function:
SELECT name, rapidfuzz_extract_one(name, ['Acme Corporation', 'Globex Industries', 'Initech', 'Soylent Green Foods'], scorer := 'token_set_ratio', score_cutoff := 80) AS matchFROM customersORDER BY id;Rows whose best score is under 80 get NULL, ready for manual review.
Spell check / typo tolerance
For a known-good dictionary, score a candidate against every entry and pick the closest. score_cutoff lets OSA give up on words that are already more than two edits away:
SELECT word, rapidfuzz_osa_distance(word, 'aple', score_cutoff := 2) AS editsFROM dictionaryWHERE rapidfuzz_osa_distance(word, 'aple', score_cutoff := 2) <= 2ORDER BY edits, wordLIMIT 5;OSA (Optimal String Alignment) handles single-character typos and adjacent transpositions naturally. With the dictionary in a list, rapidfuzz_extract(:input, words, scorer := 'osa_normalized_similarity', top_k := 5) gives the same ranking as a normalized score.
Highlight differences
rapidfuzz_levenshtein_opcodes describes both strings as equal and changed blocks β enough to render a diff:
SELECT op.operation, substr('kitten', op.s1_start + 1, op.s1_end - op.s1_start) AS from_s1, substr('sitting', op.s2_start + 1, op.s2_end - op.s2_start) AS from_s2FROM (SELECT unnest(rapidfuzz_levenshtein_opcodes('kitten', 'sitting')) AS op);Positions are 0-based character offsets, hence the + 1 for substr.
Ratio choice β visual guide
input1: 'new york city'input2: 'new york new york city'
rapidfuzz_ratio β 74.3 (extra words lower the score)rapidfuzz_partial_ratio β 100 (one is a substring)rapidfuzz_token_sort_ratio β 74.3 (sorted tokens differ in count)rapidfuzz_token_set_ratio β 100 (token sets identical)rapidfuzz_partial_token_set_ratio β 100 (sets identical, partial fallback)rapidfuzz_wratio β 90 (weighted blend, scaled for the length difference)All scorers are case-sensitive: with 'New York City' as the first input, rapidfuzz_partial_ratio drops to 80. Lowercase both sides first (see pre-normalize once, score many) when case should not matter.
The differences matter β pick the one that matches your data semantics. For names with reordered words, token_sort_ratio is usually right. For names with stray duplicates, token_set_ratio. For substring matching, partial_ratio.
Algorithm function naming
Each of the 10 metrics (Levenshtein, Damerau-Levenshtein, OSA, Indel, LCS, Hamming, Jaro, Jaro-Winkler, Prefix, Postfix) exposes 4 variants:
rapidfuzz_<algo>_distance(a, b)β raw edit count (BIGINT;DOUBLEfor Jaro and Jaro-Winkler).rapidfuzz_<algo>_similarity(a, b)β raw match count (BIGINT;DOUBLEfor Jaro and Jaro-Winkler).rapidfuzz_<algo>_normalized_distance(a, b)β[0, 1], 0 = identical.rapidfuzz_<algo>_normalized_similarity(a, b)β[0, 1], 1 = identical.
For comparisons across pairs of unequal length, the normalized_* variants are usually what you want β they donβt punish longer strings. Every variant accepts score_cutoff := n.
Platform Support
Compatibility
Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.
Quick Facts
Platforms
DuckDB 2.0.0
- Linux x86_64aarch64
- Linux (musl) Not available
- macOS Apple Silicon
- Windows x86_64x86_64 (MinGW)
- WASM Not available
Compiled binary sizes
| Platform | Architecture | Size |
|---|---|---|
| Linux | x86_64 | 4.05 MB |
| Linux | aarch64 | 3.39 MB |
| macOS | Apple Silicon | 2.86 MB |
| Windows | x86_64 | 10.25 MB |
| Windows | x86_64 (MinGW) | 17.89 MB |
Compressed download sizes for the release shown above.
DuckDB & Haybarn
Release calendar- DuckDB v2.0.0 Query.Farm repository Supported
- Haybarn v1.5.6 Built-in community repository Supported
Browser examples use Haybarn-Wasm 2.0.0-rc12.
Checked .
Learn and explore
Tutorials and articles
Writing about RapidFuzz: how it works, what changed, and why.
-
Tutorial Β· 9 min Β·
Fuzzy Matching in DuckDB: Match and Review Customer Names
Match messy customer names with RapidFuzz in DuckDB. Run the SQL in your browser, compare scorers, reduce candidate pairs, and keep ambiguous matches for review.
-
RapidFuzz for DuckDB 2.0: best-match extraction and Unicode-aware scores
RapidFuzz for DuckDB 2.0 picks the best match from a list in one call, scores non-ASCII text by character, adds Levenshtein and edit alignments, and runs column-against-constant comparisons two to four times faster.