# Add a matching key column

Shape / columns

A variant of [Merge near-duplicate values](https://meridian.online/operations/merge-near-duplicate-values).

Add a column holding a phonetic or functional key for each value, leaving the values themselves unchanged, for later matching.

Page: <https://meridian.online/operations/merge-near-duplicate-values/add-a-matching-key-column>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

add a fingerprint key and a Soundex key column and leave the values unchanged

```sql
CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, 'Alice Ng'), (2, 'ng alice'), (3, 'Ng,  Alice.'), (4, 'Bob Okafor'), (5, 'bob  okafor'), (6, ''), (7, NULL),
  (8, 'Robert'), (9, 'Rupert'), (10, 'Smith'), (11, 'Smyth'), (12, 'Ashcraft'), (13, 'Tymczak'), (14, 'Pfister')
) t(id, name);
-- Soundex: keep the first letter; code the rest with 1 to 6 (translate); H and W are dropped before coding so they do not
-- separate two letters of one code, a vowel does; adjacent repeats of a code collapse to one; the first position (the first
-- letter's own code) and the vowels are dropped; the digits are padded with zeros to three.
CREATE TEMP TABLE result AS
WITH letters AS (SELECT *, regexp_replace(upper(strip_accents(name)), '[^A-Z]', '', 'g') AS l FROM raw),
coded AS (SELECT *, string_split(translate(l[1] || replace(replace(l[2:], 'H', ''), 'W', ''),
                                          'BFPVCGJKQSXZDTLMNR', '111122222222334556'), '') AS chars FROM letters WHERE l <> ''),
deduped AS (SELECT *, list_filter(chars, lambda c, i: i = 1 OR c <> chars[i - 1]) AS d FROM coded)
SELECT r.id, r.name,
       array_to_string(list_sort(list_distinct(
         regexp_split_to_array(regexp_replace(trim(lower(strip_accents(r.name))), '[^a-z0-9\s]', '', 'g'), '\s+'))), ' ') AS fingerprint,
       d.l[1] || left(rpad(regexp_replace(array_to_string(d.d[2:], ''), '[^0-9]', '', 'g'), 3, '0'), 3) AS soundex
FROM raw r LEFT JOIN deduped d USING (id);
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'name', 'fingerprint', 'soundex'] AND (SELECT list(name ORDER BY id) FROM result) = (SELECT list(name ORDER BY id) FROM raw) AND (SELECT list(fingerprint ORDER BY id) FROM result WHERE id <= 7) = ['alice ng', 'alice ng', 'alice ng', 'bob okafor', 'bob okafor', '', NULL] AND (SELECT list(soundex ORDER BY id) FROM result WHERE id >= 8) = ['R163', 'R163', 'S530', 'S530', 'A261', 'T522', 'P236'] AND (SELECT count(*) FROM result WHERE id IN (6, 7) AND soundex IS NULL) = 2) THEN 'as expected' ELSE error('the key columns were not added with one fingerprint for every spelling of a name and the Soundex codes R163, S530, A261, T522 and P236, while the names stayed as they were') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- 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)
- Talend: [`tGenKey`](https://help.qlik.com/talend/en-US/components/8.0/identification/identification-component) (read 2026-09-27)