Skip to content

Alignment 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

Alignment

5 functions

rapidfuzz_indel_editops

Scalar function Alignment
Signature
rapidfuzz_indel_editops(s1: VARCHAR, s2: VARCHAR) → STRUCT(operation VARCHAR, s1_pos BIGINT, s2_pos BIGINT)[]
Arguments (Positional)
Argument s1 Type VARCHAR Mode Positional Description The first string.
Argument s2 Type VARCHAR Mode Positional Description The second string.
Description
1 Insertions and deletions only
SELECT rapidfuzz_indel_editops('kitten', 'sitting');

Output

rapidfuzz_indel_editops('kitten', 'sitting')
[{'operation': 'insert', 's1_pos': 0, 's2_pos': 0}, {'operation': 'delete', 's1_pos': 0, 's2_pos': 1}, {'operation': 'insert', 's1_pos': 4, 's2_pos': 4}, {'operation': 'delete', 's1_pos': 4, 's2_pos': 5}, {'operation': 'insert', 's1_pos': 6, 's2_pos': 6}]

rapidfuzz_indel_opcodes

Scalar function Alignment
Signature
rapidfuzz_indel_opcodes(s1: VARCHAR, s2: VARCHAR) → STRUCT(operation VARCHAR, s1_start BIGINT, s1_end BIGINT, s2_start BIGINT, s2_end BIGINT)[]
Arguments (Positional)
Argument s1 Type VARCHAR Mode Positional Description The first string.
Argument s2 Type VARCHAR Mode Positional Description The second string.
Description
1 Blocks without substitutions
SELECT op.operation, op.s1_start, op.s1_end, op.s2_start, op.s2_end
FROM (SELECT unnest(rapidfuzz_indel_opcodes('kitten', 'sitting')) AS op);

Output

operation s1_start s1_end s2_start s2_end
insert 0 0 0 1
delete 0 1 1 1
equal 1 4 1 4
insert 4 4 4 5
delete 4 5 5 5
equal 5 6 5 6
insert 6 6 6 7

rapidfuzz_levenshtein_editops

Scalar function Alignment
Signature
rapidfuzz_levenshtein_editops(s1: VARCHAR, s2: VARCHAR) → STRUCT(operation VARCHAR, s1_pos BIGINT, s2_pos BIGINT)[]
Arguments (Positional)
Argument s1 Type VARCHAR Mode Positional Description The first string.
Argument s2 Type VARCHAR Mode Positional Description The second string.
Description
1 Three edits
SELECT rapidfuzz_levenshtein_editops('kitten', 'sitting');

Output

rapidfuzz_levenshtein_editops('kitten', 'sitting')
[{'operation': 'replace', 's1_pos': 0, 's2_pos': 0}, {'operation': 'replace', 's1_pos': 4, 's2_pos': 4}, {'operation': 'insert', 's1_pos': 6, 's2_pos': 6}]
2 One row per edit
SELECT op.operation, op.s1_pos, op.s2_pos
FROM (SELECT unnest(rapidfuzz_levenshtein_editops('kitten', 'sitting')) AS op);

Output

operation s1_pos s2_pos
replace 0 0
replace 4 4
insert 6 6

rapidfuzz_levenshtein_opcodes

Scalar function Alignment
Signature
rapidfuzz_levenshtein_opcodes(s1: VARCHAR, s2: VARCHAR) → STRUCT(operation VARCHAR, s1_start BIGINT, s1_end BIGINT, s2_start BIGINT, s2_end BIGINT)[]
Arguments (Positional)
Argument s1 Type VARCHAR Mode Positional Description The first string.
Argument s2 Type VARCHAR Mode Positional Description The second string.
Description
1 Blocks covering both strings
SELECT op.operation, op.s1_start, op.s1_end, op.s2_start, op.s2_end
FROM (SELECT unnest(rapidfuzz_levenshtein_opcodes('kitten', 'sitting')) AS op);

Output

operation s1_start s1_end s2_start s2_end
replace 0 1 0 1
equal 1 4 1 4
replace 4 5 4 5
equal 5 6 5 6
insert 6 6 6 7

rapidfuzz_partial_ratio_alignment

Scalar function Alignment
Signature
rapidfuzz_partial_ratio_alignment(s1: VARCHAR, s2: VARCHAR) → STRUCT(score DOUBLE, s1_start BIGINT, s1_end BIGINT, s2_start BIGINT, s2_end BIGINT)
Arguments (Positional)
Argument s1 Type VARCHAR Mode Positional Description The first string.
Argument s2 Type VARCHAR Mode Positional Description The second string.
Description
1 Score and location
SELECT rapidfuzz_partial_ratio_alignment('a certain string', 'cetain');

Output

rapidfuzz_partial_ratio_alignment('a certain string', 'cetain')
{'score': 83.33333333333334, 's1_start': 2, 's1_end': 8, 's2_start': 0, 's2_end': 6}
2 Extract the matching substring
SELECT substr(s, a.s1_start + 1, a.s1_end - a.s1_start) AS matched
FROM (SELECT 'the quick brown fox' AS s,
             rapidfuzz_partial_ratio_alignment(s, 'quack') AS a);

Output

matched
quick