# Merge near-duplicate values Shape / columns cluster near-duplicate values within one column and merge them Page: ## Status Runs as a SQL step. arcform carries it as a `sql:` step today. ## The DuckDB step the fingerprint key-collision method It reads the `orders` table the proofs share. Create it first: ```sql CREATE TABLE orders AS SELECT * FROM (VALUES (1, 'Alice Ng', 'north', DATE '2026-01-03', 120.00, 'widget', 'a|b', '{"channel":"web","items":[1,2]}'), (2, 'alice ng', 'north', DATE '2026-01-05', 80.50, 'gadget', 'b', '{"channel":"shop","items":[3]}'), (3, 'Bob Okafor', 'south', DATE '2026-01-05', 300.00, 'widget', 'a|c', '{"channel":"web","items":[4,5,6]}'), (4, 'Carla Díaz', 'east', DATE '2026-01-09', NULL, 'gizmo', NULL, '{"channel":"web","items":[]}'), (5, 'Bob Okafor', 'south', DATE '2026-01-12', 45.25, 'gadget', 'c', '{"channel":"phone","items":[7]}'), (6, 'Dev Patel', 'west', DATE '2026-01-12', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (7, 'Carla Diaz', 'east', DATE '2026-01-20', 99.99, 'gizmo', 'b|c', '{"channel":"shop","items":[10]}'), (8, 'Dev Patel', 'west', DATE '2026-02-02', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (9, 'Eve Larsen', NULL, DATE '2026-02-03', 15.00, 'gadget', NULL, '{"channel":"web","items":[11]}'), (10, 'Alice Ng', 'north', DATE '2026-02-10', 220.00, 'gizmo', 'a|b|c', '{"channel":"phone","items":[12,13]}') ) t(order_id, customer, region, order_date, amount, product, tags, meta); ``` Then run the statement: ```sql CREATE TEMP TABLE result AS WITH keyed AS ( SELECT customer, array_to_string(list_sort(list_distinct( regexp_split_to_array(regexp_replace(trim(lower(strip_accents(customer))), '[^a-z0-9\s]', '', 'g'), '\s+'))), ' ') AS fingerprint FROM orders) SELECT fingerprint, list_sort(list(DISTINCT customer)) AS variants, count(*) AS n FROM keyed GROUP BY fingerprint HAVING count(DISTINCT customer) > 1; SELECT * FROM result ORDER BY fingerprint; SELECT CASE WHEN ((SELECT map_from_entries(list((fingerprint, variants) ORDER BY fingerprint)) = MAP {'alice ng': ['Alice Ng', 'alice ng'], 'bob okafor': ['Bob Okafor', 'Bob Okafor'], 'carla diaz': ['Carla Diaz', 'Carla Díaz']} FROM result)) THEN 'as expected' ELSE error('the three names written two ways were not found as three clusters') END AS verdict; ``` It needs nothing installed beyond DuckDB itself. ## Variants - Group values that share a key: Cluster values whose normalised fingerprint or phonetic key is equal, then merge each cluster to one value. - [Add a matching key column](https://meridian.online/operations/merge-near-duplicate-values/add-a-matching-key-column): Add a column holding a phonetic or functional key for each value, leaving the values themselves unchanged, for later matching. - [Group values within a distance](https://meridian.online/operations/merge-near-duplicate-values/group-values-within-a-distance): Cluster values whose edit or similarity distance falls within a threshold, then merge each cluster to one value. - Standardise values against a reference: Map each variant to the closest valid value from a reference list or a language model, rather than to a cluster's own value. ## What other tools call it Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2). - Alteryx Designer: [`Precision Match`](https://help.alteryx.com/current/en/designer/tools/ai-tools/precision-match-tool.html) (read 2026-09-27) - OpenRefine: [`Beider-Morse`](https://openrefine.org/docs/manual/cellediting#baider-morse) (read 2026-09-27) - OpenRefine: [`Cluster and edit…`](https://openrefine.org/docs/manual/cellediting#cluster-and-edit) (read 2026-09-27) - OpenRefine: [`Cologne Phonetic`](https://openrefine.org/docs/manual/cellediting#cologne-fingerprinting) (read 2026-09-27) - OpenRefine: [`Custom clustering methods`](https://openrefine.org/docs/manual/cellediting#custom-clustering-methods) (read 2026-09-27) - OpenRefine: [`Daitch-Mokotoff`](https://openrefine.org/docs/manual/cellediting#daitch-mokotoff) (read 2026-09-27) - OpenRefine: [`Fingerprint`](https://openrefine.org/docs/manual/cellediting#fingerprinting) (read 2026-09-27) - OpenRefine: [`Levenshtein`](https://openrefine.org/docs/manual/cellediting#levenshtein-distance) (read 2026-09-27) - OpenRefine: [`Metaphone3`](https://openrefine.org/docs/manual/cellediting#metaphone3-fingerprinting) (read 2026-09-27) - OpenRefine: [`N-gram Fingerprint`](https://openrefine.org/docs/manual/cellediting#n-gram) (read 2026-09-27) - OpenRefine: [`PPM`](https://openrefine.org/docs/manual/cellediting#ppm) (read 2026-09-27) - Power Query M: [`Table.AddFuzzyClusterColumn`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#transformation) (read 2026-09-27) - RapidMiner Studio: [`Generate Phonetic Encodings`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/attribute_generation/phonetic_encoding.html) (read 2026-09-27) - Tableau Prep: [`Common Characters`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Group Values`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Pronunciation`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Pronunciation + Spelling`](https://help.tableau.com/current/prep/en-us/prep_validate_data.htm) (read 2026-09-27) - Tableau Prep: [`Spelling`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Talend: [`tGenKey`](https://help.qlik.com/talend/en-US/components/8.0/identification/identification-component) (read 2026-09-27) - Trifacta: [`Standardize column`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)