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 functionsPick the best matches for a string from a list of choices, like Python's rapidfuzz.process.extract and extractOne, ranked by any ratio or normalized similarity.
Signature
Arguments
| Argument | Type | Mode | Description |
|---|---|---|---|
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 (' |
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
The best matches for query among choices, best first, like Python's rapidfuzz.process.extract. Returns a list of up to top_k structs with the choice, its score and its 1-based choice_index in choices; ties keep the order of choices. The query is preprocessed once and reused for every choice. Strings are compared by Unicode character, not by UTF-8 byte.
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}] |
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 |
Signature
Arguments
| Argument | Type | Mode | Description |
|---|---|---|---|
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 (' |
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
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. Ties go to the earlier choice. Strings are compared by Unicode character, not by UTF-8 byte.
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} |
SELECT rapidfuzz_extract_one('zzz', ['New York Jets', 'New York Giants', 'Dallas Cowboys'], score_cutoff := 50) AS best;
Output
| best |
|---|
| NULL |
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 |