Levenshtein 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
Levenshtein
4 functionsInsertions, deletions and substitutions, each with a configurable cost. Counts characters, unlike DuckDB's built-in byte-based levenshtein.
Signature
Arguments
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Argument
insert_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of inserting a character. |
Argument
delete_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of deleting a character. |
Argument
replace_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of substituting a character. A cost of 2 or more makes a substitution no cheaper than a deletion plus an insertion. |
Argument
score_cutoff
|
Type
BIGINT
|
Mode Named | Description Default: NULL Distances above this value are returned as score_cutoff + 1, which lets RapidFuzz skip work on poor matches. NULL means no cutoff. |
Description
Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. Unlike DuckDB's built-in levenshtein, it counts Unicode characters rather than bytes, and each kind of edit can be weighted. Returns the distance: 0 means identical. Returns BIGINT. Strings are compared by Unicode character, not by UTF-8 byte.
SELECT rapidfuzz_levenshtein_distance('kitten', 'sitting');
Output
| rapidfuzz_levenshtein_distance('kitten', 'sitting') |
|---|
| 3 |
SELECT rapidfuzz_levenshtein_distance('kitten', 'sitting', replace_cost := 2);
Output
| rapidfuzz_levenshtein_distance('kitten', 'sitting', replace_cost := 2) |
|---|
| 5 |
SELECT rapidfuzz_levenshtein_distance('café', 'cafe') AS rapidfuzz,
levenshtein('café', 'cafe') AS builtin;
Output
| rapidfuzz | builtin |
|---|---|
| 1 | 2 |
Related functions
- rapidfuzz_levenshtein_similarity() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_normalized_distance() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_normalized_similarity() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
Signature
Arguments
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Argument
insert_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of inserting a character. |
Argument
delete_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of deleting a character. |
Argument
replace_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of substituting a character. A cost of 2 or more makes a substitution no cheaper than a deletion plus an insertion. |
Argument
score_cutoff
|
Type
DOUBLE
|
Mode Named | Description Default: NULL Distances above this value are returned as 1, which lets RapidFuzz skip work on poor matches. Must be between 0 and 1. NULL means no cutoff. |
Description
Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. Unlike DuckDB's built-in levenshtein, it counts Unicode characters rather than bytes, and each kind of edit can be weighted. Returns the distance scaled from 0 (identical) to 1. Returns DOUBLE. Strings are compared by Unicode character, not by UTF-8 byte.
SELECT rapidfuzz_levenshtein_normalized_distance('kitten', 'sitting');
Output
| rapidfuzz_levenshtein_normalized_distance('kitten', 'sitting') |
|---|
| 0.42857142857142855 |
Related functions
- rapidfuzz_levenshtein_distance() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_similarity() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_normalized_similarity() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
Signature
Arguments
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Argument
insert_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of inserting a character. |
Argument
delete_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of deleting a character. |
Argument
replace_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of substituting a character. A cost of 2 or more makes a substitution no cheaper than a deletion plus an insertion. |
Argument
score_cutoff
|
Type
DOUBLE
|
Mode Named | Description Default: NULL Similarities below this value are returned as 0, which lets RapidFuzz skip work on poor matches. Must be between 0 and 1. NULL means no cutoff. |
Description
Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. Unlike DuckDB's built-in levenshtein, it counts Unicode characters rather than bytes, and each kind of edit can be weighted. Returns the similarity scaled from 0 to 1 (identical). Returns DOUBLE. Strings are compared by Unicode character, not by UTF-8 byte.
SELECT rapidfuzz_levenshtein_normalized_similarity('kitten', 'sitting');
Output
| rapidfuzz_levenshtein_normalized_similarity('kitten', 'sitting') |
|---|
| 0.5714285714285714 |
Related functions
- rapidfuzz_levenshtein_distance() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_similarity() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_normalized_distance() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
Signature
Arguments
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Argument
insert_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of inserting a character. |
Argument
delete_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of deleting a character. |
Argument
replace_cost
|
Type
BIGINT
|
Mode Named | Description Default: 1 Cost of substituting a character. A cost of 2 or more makes a substitution no cheaper than a deletion plus an insertion. |
Argument
score_cutoff
|
Type
BIGINT
|
Mode Named | Description Default: NULL Similarities below this value are returned as 0, which lets RapidFuzz skip work on poor matches. NULL means no cutoff. |
Description
Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2. Unlike DuckDB's built-in levenshtein, it counts Unicode characters rather than bytes, and each kind of edit can be weighted. Returns the similarity: higher is more similar. Returns BIGINT. Strings are compared by Unicode character, not by UTF-8 byte.
SELECT rapidfuzz_levenshtein_similarity('kitten', 'sitting');
Output
| rapidfuzz_levenshtein_similarity('kitten', 'sitting') |
|---|
| 4 |
Related functions
- rapidfuzz_levenshtein_distance() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_normalized_distance() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2
- rapidfuzz_levenshtein_normalized_similarity() — Levenshtein distance: the minimum number of single-character insertions, deletions and substitutions that turn s1 into s2