Fuzzy Name Matching With SOUNDEX and DIFFERENCE

Fuzzy name matching with SOUNDEX and DIFFERENCE finds names that sound alike, not customers who are the same person. Treat every hit as a question for a human, never as an answer.

The duplicate customers nobody can find

Your sales team says the customer list has duplicates. “Smith” and “Smyth” at the same address, probably typed by two different people. An exact match will never find them, because the spellings differ.

SQL Server has two old but handy functions for this. SOUNDEX turns a name into a four-character sound code. DIFFERENCE compares two sound codes and returns a score from 0 to 4, where 4 means the most alike.

Let me build a small customer list. Two postal codes, four names. The query shows the sound code for each name.

DROP TABLE IF EXISTS #NameCandidates;

CREATE TABLE #NameCandidates (
    Id         int PRIMARY KEY,
    Name       nvarchar(60),
    PostalCode varchar(12));

INSERT #NameCandidates VALUES
    (1, N'Smith', '10001'), (2, N'Smyth', '10001'),
    (3, N'Lee',   '10002'), (4, N'Li',    '10002');

SELECT Id, Name, SOUNDEX(Name) AS PhoneticCode
FROM #NameCandidates
ORDER BY Id;

Smith and Smyth share the code S530. Lee and Li share L000. That is the whole trick. Names that sound alike get the same code.

Score the pairs inside a block

Now compare names pairwise. Two details matter here. The join requires the second Id to be greater than the first, so each pair appears once. And it only compares names inside the same postal code, which I call a block.

SELECT a.Id AS FirstId, b.Id AS SecondId,
       a.Name AS FirstName, b.Name AS SecondName,
       DIFFERENCE(a.Name, b.Name) AS PhoneticScore
FROM #NameCandidates AS a
JOIN #NameCandidates AS b
  ON b.PostalCode = a.PostalCode AND b.Id > a.Id
WHERE DIFFERENCE(a.Name, b.Name) >= 3
ORDER BY PhoneticScore DESC, a.Id, b.Id;

SELECT SOUNDEX(N'Li') AS LiCode, SOUNDEX(N'Lo') AS LoCode,
       DIFFERENCE(N'Li', N'Lo') AS DifferentNamesScore;

SELECT SOUNDEX(CONVERT(nvarchar(20), NULL)) AS NullCode,
       DIFFERENCE(N'Smith', CONVERT(nvarchar(20), NULL)) AS NullScore;

The second grid has two candidates: Smith with Smyth, and Lee with Li. Both score 4. Smith and Smyth look like a real duplicate. Lee and Li might be two different people.

The third grid proves the point. Li and Lo are clearly different names, yet they have the same code and score 4. The last grid shows a NULL name gives a NULL code and a NULL score. Missing data never matches anything.

Where the scale fools you

The score has only five levels, and the cutoff is a guess. Look at a few pairs side by side.

SELECT a AS NameA, b AS NameB, SOUNDEX(a) AS CodeA, SOUNDEX(b) AS CodeB,
       DIFFERENCE(a, b) AS Score
FROM (VALUES (N'Smith', N'Smythe'), (N'Smith', N'Jones'),
             (N'Catherine', N'Katherine'), (N'Robert', N'Rupert')) AS v (a, b)
ORDER BY Score DESC, a;

Smith and Jones score 2, which is low but not zero. Catherine and Katherine score only 3, because SOUNDEX keeps the first letter, and C is not K. A cutoff of 4 would miss them. A cutoff of 3 catches them and lets in more noise. Test your own cutoff against names you already know are matches and non-matches.

Why I compare inside a block

Comparing every name with every other name explodes fast. Count the pairs.

SELECT COUNT(*) AS AllPairs,
       SUM(CASE WHEN a.PostalCode = b.PostalCode THEN 1 ELSE 0 END) AS SamePostalCodePairs
FROM #NameCandidates AS a
JOIN #NameCandidates AS b ON b.Id > a.Id;

SELECT CAST(100000 AS bigint) * 99999 / 2 AS PairsFor100000Customers;

Four names make 6 pairs, and only 2 share a postal code. For 100,000 customers the count is 4,999,950,000. Blocking cuts that to something you can run. The price is that a customer who moved will never be compared with their old record, so pick a block that fits your review process.

Suggestion, not identity

SOUNDEX was built around English names and is weak elsewhere. Keep the candidate ids, the blocking rule, the score and the human decision. Add evidence like an exact address or a customer number. And never overwrite a name just to improve a score.

DROP TABLE IF EXISTS #NameCandidates;

Let the score shorten the review list, then let a person decide.

A phonetic match is not an identity match, it is a candidate to inspect.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.