Extract references and compare names with SQL

A file may contain invoice references inside free text and customer names with inconsistent spacing or spelling. AltDat’s dedicated text cards cover each step. A SQL query lets you combine the steps and choose exactly which columns to return.

For a flow built from input cards, follow Text and list actions. This tutorial uses Query a table with SQL, which requires a paid AltDat plan (Starter or higher). Public connector certification is pending; private testers can use their imported connector.

Start with three records

Download customer-text.csv, read it with Get file content, and pass it to Prepare a table with Source Format csv.

The file has raw_reference, name, reference_name, and payload columns. The two names in each row are already candidate pairs. This example compares those pairs; it does not search every name against every other name.

Set the SQL action’s AltDat Table Content to Prepare’s Body. Enter:

WITH cleaned AS (
    SELECT *,
        upper(trim(name)) AS cleaned_name,
        upper(trim(reference_name)) AS cleaned_reference
    FROM data
)
SELECT
    record_id,
    nullif(regexp_extract(raw_reference, 'INV-[0-9]+'), '') AS invoice_id,
    levenshtein(cleaned_name, cleaned_reference) AS edit_distance,
    round(jaro_winkler_similarity(cleaned_name, cleaned_reference), 4) AS similarity,
    coalesce(json_extract_string(payload, '$.customer.segment'), 'unknown') AS segment
FROM cleaned
ORDER BY record_id

Read the results

The first record produces invoice ID INV-1042, edit distance 2, similarity about 0.9611, and segment pro. Its names are MARTHA and MARHTA after trimming and case conversion. Swapping the two letters takes two edits under Levenshtein distance.

The second produces INV-1043, distance 0, similarity 1, and segment unknown. Normalization makes Ada and ADA equal. The third has no matching invoice reference, so invoice_id is null.

Each function has a separate purpose:

• trim removes surrounding whitespace, and upper removes case differences for this comparison. Neither changes punctuation or abbreviations.
• regexp_extract returns the first match, or empty text if nothing matches. nullif(..., '') turns that empty result into null. To extract only the digits, use regexp_extract(raw_reference, 'INV-([0-9]+)', 1).
• levenshtein counts insertions, deletions, and substitutions. Lower values mean fewer edits; longer names can naturally require more edits.
• jaro_winkler_similarity produces a score between 0 and 1, with higher scores indicating closer text. It gives extra weight to matching prefixes.
• json_extract_string reads a JSON path. coalesce supplies the word unknown when the result is null. It does not make malformed JSON valid.

The regex engine uses RE2 syntax. Lookbehind and backreferences inside the pattern are not supported. Capture groups are supported.

Choose review rules with your own examples

A high similarity score is a useful review signal, not proof of identity. Check exact identifiers first when available. Evaluate a threshold against known matching and nonmatching pairs from your own data before using it to merge or update records. Missing names yield null scores.

SQL returns a result object with rows in data. In Power Automate, Parse JSON can expose invoice_id, edit_distance, similarity, and segment as named fields. For a table you can immediately export to Excel, chain the corresponding form cards instead.

A SQL request has one query credit charge based on input rows. A chain of separate cards has one charge per card, while Run a transformation pipeline groups supported operations into one transform request, charged once for every five operations. Choose the form that fits your flow and check the live pricing matrix.

See DuckDB’s text functions and regular expression functions for the SQL details.