Lesson 7.6

Fuzzy Matching in SQL

levenshtein counts the single-letter edits between two strings. A small count finds a name typed with a mistake, and a match is a candidate to check, not an answer.

A caller spells their surname for a support agent, who types castelano. The customer is Castellano, with two l's, so = finds nobody, and so does LIKE, which needs the right letters around its wildcards. What you want is the names that are close.

levenshtein(a, b) measures closeness as an edit distance: how many single-letter changes (add one, remove one, or swap one for another) turn one string into the other. Castellano to Castelano is 1 edit, a removed l. Sort by it and the likely matches come first:

Pattern

select customer_id,
       first_name,
       last_name,
       levenshtein(lower(last_name), 'castelano') as edits
from customers
order by edits, customer_id
limit 3

Both Castellanos are 1 edit away, and the next customer, Patel, is 5. A gap like that is what a real typo looks like.

Case counts as an edit

levenshtein compares characters exactly, so a capital C and a small c are different letters:

One edit, or two

select levenshtein('Castellano', 'Castelano') as same_case,
       levenshtein('Castellano', 'castelano') as different_case

That returns 1 and 2. Lowercase both sides, as the first query does, so the count measures the typo and not the capitals.

A close match isn't always the same person

Edit distance doesn't know how long the names are. Two edits in Castellano still leave eight letters in place, but two edits in Ford leave two of four. Look for likely duplicate customers with a self-join and that's what turns up:

Likely duplicates, or not

select a.customer_id as id_a,
       a.last_name   as name_a,
       a.city        as city_a,
       b.customer_id as id_b,
       b.last_name   as name_b,
       b.city        as city_b,
       levenshtein(a.last_name, b.last_name) as edits
from customers as a
join customers as b
  on a.customer_id < b.customer_id
where a.last_name <> b.last_name
  and levenshtein(a.last_name, b.last_name) <= 2

Isabella Ford in Austin and Daniel Foss in Tacoma are 2 edits apart and clearly two people. Treat every fuzzy match as a candidate, and confirm it with another column (a city, an email, a phone number) before you merge or update anything.

A score from 0 to 1

jaro_winkler_similarity(a, b) scores two strings from 0 (nothing in common) to 1 (identical), and weights a shared start more heavily, which suits names. Because it's a share rather than a count, it accounts for length: Castellano and Castelano score 0.98, while Ford and Foss score 0.67.

Edits and similarity side by side

select last_name,
       levenshtein(lower(last_name), 'fors') as edits,
       round(jaro_winkler_similarity(lower(last_name), 'fors'), 2) as similarity
from customers
where last_name in ('Ford', 'Foss', 'Holt')
order by similarity desc

For fors, Ford scores 0.88, Foss 0.87, and Holt 0.5. Neither function can tell you which of the first two the caller meant: only another column can. DuckDB also has damerau_levenshtein, which counts two neighboring letters typed in the wrong order as one edit instead of two.

In other databases

PostgreSQL has levenshtein in the fuzzystrmatch extension, and a trigram similarity score in pg_trgm. Both need CREATE EXTENSION first.
Snowflake has EDITDISTANCE and JAROWINKLER_SIMILARITY, which scores from 0 to 100. BigQuery has EDIT_DISTANCE.
MySQL and SQLite have no edit distance built in. SQL Server's long-standing option is SOUNDEX, which compares how names sound in English and is much rougher.

Syntax reference

Need syntax details? See the SQL string functions reference

Schema · Garden Shop Table · customers 9 columns · 20 rows

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Your task

A support agent typed three surnames while on calls: castelano, fors, and tomson. The starter lists them. Match each one to the customers whose lowercased last_name is within 2 edits of it. Return typed, customer_id, first_name, last_name, and the distance as edits. Sort by typed, then edits, then customer_id.