Skip to content

Best Match functions in the RapidFuzz DuckDB extension

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

Function category

Best Match

2 functions

rapidfuzz_extract

Scalar function Best Match
Signature
rapidfuzz_extract( query: VARCHAR, choices: VARCHAR[], score_cutoff := DOUBLE = NULL, scorer := VARCHAR = 'wratio', top_k := BIGINT = 5 ) → STRUCT(choice VARCHAR, score DOUBLE, choice_index BIGINT)[]
Arguments
Argument query Type VARCHAR Mode Positional Description The string to look up.
Argument choices Type VARCHAR[] Mode Positional Description The candidates. NULL entries are skipped but keep their position.
Argument scorer Type VARCHAR Mode Named Description Default: 'wratio' The similarity used to rank the choices: any ratio name ('ratio', 'partial_ratio', 'token_sort_ratio', 'partial_token_sort_ratio', 'token_set_ratio', 'partial_token_set_ratio', 'token_ratio', 'partial_token_ratio', 'wratio', 'qratio') or a normalized similarity ('_normalized_similarity', e.g. 'levenshtein_normalized_similarity', 'jaro_winkler_normalized_similarity'). Case-insensitive.
Argument top_k Type BIGINT Mode Named Description Default: 5 The maximum number of matches to return. NULL returns all of them.
Argument score_cutoff Type DOUBLE Mode Named Description Default: NULL Choices scoring below this value are skipped. The range follows the scorer: 0–100 for ratios, 0–1 for normalized similarities. NULL means no cutoff.
Description
1 The two best matches by sorted words
SELECT rapidfuzz_extract('new york jets', ['New York Jets', 'New York Giants', 'Dallas Cowboys'],
                         scorer := 'token_sort_ratio', top_k := 2) AS matches;

Output

matches
[{'choice': 'New York Jets', 'score': 76.92307692307692, 'choice_index': 1}, {'choice': 'New York Giants', 'score': 64.28571428571428, 'choice_index': 2}]
2 One row per match
SELECT m.choice, round(m.score, 1) AS score, m.choice_index
FROM (SELECT unnest(rapidfuzz_extract('cowboys', ['New York Jets', 'New York Giants', 'Dallas Cowboys'], score_cutoff := 25)) AS m);

Output

choice score choice_index
Dallas Cowboys 83.1 3
New York Jets 30.0 1
New York Giants 27.7 2

rapidfuzz_extract_one

Scalar function Best Match
Signature
rapidfuzz_extract_one( query: VARCHAR, choices: VARCHAR[], score_cutoff := DOUBLE = NULL, scorer := VARCHAR = 'wratio' ) → STRUCT(choice VARCHAR, score DOUBLE, choice_index BIGINT)
Arguments
Argument query Type VARCHAR Mode Positional Description The string to look up.
Argument choices Type VARCHAR[] Mode Positional Description The candidates. NULL entries are skipped but keep their position.
Argument scorer Type VARCHAR Mode Named Description Default: 'wratio' The similarity used to rank the choices: any ratio name ('ratio', 'partial_ratio', 'token_sort_ratio', 'partial_token_sort_ratio', 'token_set_ratio', 'partial_token_set_ratio', 'token_ratio', 'partial_token_ratio', 'wratio', 'qratio') or a normalized similarity ('_normalized_similarity', e.g. 'levenshtein_normalized_similarity', 'jaro_winkler_normalized_similarity'). Case-insensitive.
Argument score_cutoff Type DOUBLE Mode Named Description Default: NULL Choices scoring below this value are skipped. The range follows the scorer: 0–100 for ratios, 0–1 for normalized similarities. NULL means no cutoff.
Description
1 The best match by weighted ratio
SELECT rapidfuzz_extract_one('cowboys', ['New York Jets', 'New York Giants', 'Dallas Cowboys']) AS best;

Output

best
{'choice': 'Dallas Cowboys', 'score': 83.07692307692308, 'choice_index': 3}
2 Nothing reaches the cutoff
SELECT rapidfuzz_extract_one('zzz', ['New York Jets', 'New York Giants', 'Dallas Cowboys'], score_cutoff := 50) AS best;

Output

best
NULL
3 Map every input to its closest known value
SELECT name, rapidfuzz_extract_one(name, ['Acme Corporation', 'Globex Industries', 'Initech'],
                                   scorer := 'token_set_ratio').choice AS canonical
FROM customers
ORDER BY id
LIMIT 4;

Output

name canonical
Acme Corporation Acme Corporation
ACME Corp. Acme Corporation
Acme Corp Acme Corporation
Globex Industries Globex Industries