Skip to content

Damerau-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

Damerau-Levenshtein

4 functions

rapidfuzz_damerau_levenshtein_distance

Scalar function Damerau-Levenshtein
Signature
rapidfuzz_damerau_levenshtein_distance( s1: VARCHAR, s2: VARCHAR, 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 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 'ca' and 'abc'
SELECT rapidfuzz_damerau_levenshtein_distance('ca', 'abc');

Output

rapidfuzz_damerau_levenshtein_distance('ca', 'abc')
2
2 Damerau-Levenshtein vs. OSA
SELECT rapidfuzz_damerau_levenshtein_distance('ca', 'abc') AS damerau_levenshtein,
       rapidfuzz_osa_distance('ca', 'abc') AS osa;

Output

damerau_levenshtein osa
2 3

rapidfuzz_damerau_levenshtein_normalized_distance

Scalar function Damerau-Levenshtein
Signature
rapidfuzz_damerau_levenshtein_normalized_distance( s1: VARCHAR, s2: VARCHAR, 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 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 'ca' and 'abc'
SELECT rapidfuzz_damerau_levenshtein_normalized_distance('ca', 'abc');

Output

rapidfuzz_damerau_levenshtein_normalized_distance('ca', 'abc')
0.6666666666666666

rapidfuzz_damerau_levenshtein_normalized_similarity

Scalar function Damerau-Levenshtein
Signature
rapidfuzz_damerau_levenshtein_normalized_similarity( s1: VARCHAR, s2: VARCHAR, 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 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 'ca' and 'abc'
SELECT rapidfuzz_damerau_levenshtein_normalized_similarity('ca', 'abc');

Output

rapidfuzz_damerau_levenshtein_normalized_similarity('ca', 'abc')
0.33333333333333337

rapidfuzz_damerau_levenshtein_similarity

Scalar function Damerau-Levenshtein
Signature
rapidfuzz_damerau_levenshtein_similarity( s1: VARCHAR, s2: VARCHAR, 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 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 'ca' and 'abc'
SELECT rapidfuzz_damerau_levenshtein_similarity('ca', 'abc');

Output

rapidfuzz_damerau_levenshtein_similarity('ca', 'abc')
1