Skip to content

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 functions

rapidfuzz_levenshtein_distance

Scalar function Levenshtein
Signature
rapidfuzz_levenshtein_distance( s1: VARCHAR, s2: VARCHAR, delete_cost := BIGINT = 1, insert_cost := BIGINT = 1, replace_cost := BIGINT = 1, score_cutoff := BIGINT = NULL ) → BIGINT
Arguments
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
1 The distance of 'kitten' and 'sitting'
SELECT rapidfuzz_levenshtein_distance('kitten', 'sitting');

Output

rapidfuzz_levenshtein_distance('kitten', 'sitting')
3
2 Weight substitutions double
SELECT rapidfuzz_levenshtein_distance('kitten', 'sitting', replace_cost := 2);

Output

rapidfuzz_levenshtein_distance('kitten', 'sitting', replace_cost := 2)
5
3 Characters, not bytes: one edit
SELECT rapidfuzz_levenshtein_distance('café', 'cafe') AS rapidfuzz,
       levenshtein('café', 'cafe') AS builtin;

Output

rapidfuzz builtin
1 2

rapidfuzz_levenshtein_normalized_distance

Scalar function Levenshtein
Signature
rapidfuzz_levenshtein_normalized_distance( s1: VARCHAR, s2: VARCHAR, delete_cost := BIGINT = 1, insert_cost := BIGINT = 1, replace_cost := BIGINT = 1, score_cutoff := DOUBLE = NULL ) → DOUBLE
Arguments
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
1 The normalized distance of 'kitten' and 'sitting'
SELECT rapidfuzz_levenshtein_normalized_distance('kitten', 'sitting');

Output

rapidfuzz_levenshtein_normalized_distance('kitten', 'sitting')
0.42857142857142855

rapidfuzz_levenshtein_normalized_similarity

Scalar function Levenshtein
Signature
rapidfuzz_levenshtein_normalized_similarity( s1: VARCHAR, s2: VARCHAR, delete_cost := BIGINT = 1, insert_cost := BIGINT = 1, replace_cost := BIGINT = 1, score_cutoff := DOUBLE = NULL ) → DOUBLE
Arguments
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
1 The normalized similarity of 'kitten' and 'sitting'
SELECT rapidfuzz_levenshtein_normalized_similarity('kitten', 'sitting');

Output

rapidfuzz_levenshtein_normalized_similarity('kitten', 'sitting')
0.5714285714285714

rapidfuzz_levenshtein_similarity

Scalar function Levenshtein
Signature
rapidfuzz_levenshtein_similarity( s1: VARCHAR, s2: VARCHAR, delete_cost := BIGINT = 1, insert_cost := BIGINT = 1, replace_cost := BIGINT = 1, score_cutoff := BIGINT = NULL ) → BIGINT
Arguments
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
1 The similarity of 'kitten' and 'sitting'
SELECT rapidfuzz_levenshtein_similarity('kitten', 'sitting');

Output

rapidfuzz_levenshtein_similarity('kitten', 'sitting')
4