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 functionsWhere two strings differ: the edit operations, the equal/changed blocks covering both strings, and where rapidfuzz_partial_ratio found its match. Positions are 0-based character offsets.
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Description
The Indel edit operations ('insert' or 'delete', no substitutions) that turn s1 into s2, as structs with operation, s1_pos and s2_pos. Positions are 0-based character offsets and ranges are half-open, as in Python's rapidfuzz.
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}] |
Related functions
- rapidfuzz_levenshtein_editops() — The Levenshtein edit operations that turn s1 into s2: a list of structs with the `operation` ('insert', 'delete' or 'replace') and the positions `s1_pos` and `s2_pos` it applies at
- rapidfuzz_levenshtein_opcodes() — Describes how to turn s1 into s2 as blocks that cover both strings: each struct has an `operation` ('equal', 'insert', 'delete' or 'replace') and the ranges `s1_start`–`s1_end` and `s2_start`–`s2_end`
- rapidfuzz_indel_opcodes() — Describes how to turn s1 into s2 as 'equal', 'insert' and 'delete' blocks covering both strings, based on Indel edits
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Description
Describes how to turn s1 into s2 as 'equal', 'insert' and 'delete' blocks covering both strings, based on Indel edits. Each struct has operation, s1_start, s1_end, s2_start and s2_end. Positions are 0-based character offsets and ranges are half-open, as in Python's rapidfuzz.
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 |
Related functions
- rapidfuzz_levenshtein_editops() — The Levenshtein edit operations that turn s1 into s2: a list of structs with the `operation` ('insert', 'delete' or 'replace') and the positions `s1_pos` and `s2_pos` it applies at
- rapidfuzz_levenshtein_opcodes() — Describes how to turn s1 into s2 as blocks that cover both strings: each struct has an `operation` ('equal', 'insert', 'delete' or 'replace') and the ranges `s1_start`–`s1_end` and `s2_start`–`s2_end`
- rapidfuzz_indel_editops() — The Indel edit operations ('insert' or 'delete', no substitutions) that turn s1 into s2, as structs with `operation`, `s1_pos` and `s2_pos`
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Description
The Levenshtein edit operations that turn s1 into s2: a list of structs with the operation ('insert', 'delete' or 'replace') and the positions s1_pos and s2_pos it applies at. Positions are 0-based character offsets and ranges are half-open, as in Python's rapidfuzz.
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}] |
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 |
Related functions
- rapidfuzz_levenshtein_opcodes() — Describes how to turn s1 into s2 as blocks that cover both strings: each struct has an `operation` ('equal', 'insert', 'delete' or 'replace') and the ranges `s1_start`–`s1_end` and `s2_start`–`s2_end`
- rapidfuzz_indel_editops() — The Indel edit operations ('insert' or 'delete', no substitutions) that turn s1 into s2, as structs with `operation`, `s1_pos` and `s2_pos`
- rapidfuzz_indel_opcodes() — Describes how to turn s1 into s2 as 'equal', 'insert' and 'delete' blocks covering both strings, based on Indel edits
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Description
Describes how to turn s1 into s2 as blocks that cover both strings: each struct has an operation ('equal', 'insert', 'delete' or 'replace') and the ranges s1_start–s1_end and s2_start–s2_end. Useful for highlighting differences. Positions are 0-based character offsets and ranges are half-open, as in Python's rapidfuzz.
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 |
Related functions
- rapidfuzz_levenshtein_editops() — The Levenshtein edit operations that turn s1 into s2: a list of structs with the `operation` ('insert', 'delete' or 'replace') and the positions `s1_pos` and `s2_pos` it applies at
- rapidfuzz_indel_editops() — The Indel edit operations ('insert' or 'delete', no substitutions) that turn s1 into s2, as structs with `operation`, `s1_pos` and `s2_pos`
- rapidfuzz_indel_opcodes() — Describes how to turn s1 into s2 as 'equal', 'insert' and 'delete' blocks covering both strings, based on Indel edits
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
s1
|
Type
VARCHAR
|
Mode Positional | Description The first string. |
Argument
s2
|
Type
VARCHAR
|
Mode Positional | Description The second string. |
Description
The rapidfuzz_partial_ratio score together with where it was found: a struct with score and the ranges s1_start–s1_end and s2_start–s2_end of the best-matching substrings. Positions are 0-based character offsets and ranges are half-open, as in Python's rapidfuzz.
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} |
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 |
Related functions
- rapidfuzz_levenshtein_editops() — The Levenshtein edit operations that turn s1 into s2: a list of structs with the `operation` ('insert', 'delete' or 'replace') and the positions `s1_pos` and `s2_pos` it applies at
- rapidfuzz_levenshtein_opcodes() — Describes how to turn s1 into s2 as blocks that cover both strings: each struct has an `operation` ('equal', 'insert', 'delete' or 'replace') and the ranges `s1_start`–`s1_end` and `s2_start`–`s2_end`
- rapidfuzz_indel_editops() — The Indel edit operations ('insert' or 'delete', no substitutions) that turn s1 into s2, as structs with `operation`, `s1_pos` and `s2_pos`