Skip to content

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.

1,905,713
extension loads Β· last 90 days
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;

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);

Expected result

name canonical
ACME Corp. Acme Corporation
Globex Industires Globex Industries
Initech LLC Initech

Common tasks

Deep Dive

Technical Details

Reference

Extension Contents

Quick reference to all available functions and settings organized by category.

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

API Reference

Function Reference

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

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

Release status Stable
Software License MIT
Pricing Free
Written In C++
Source Available Yes
View on GitHub
Usage
1,905,713
loads Β· last 90 days

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.