EDIT_DISTANCE and JARO_WINKLER: Fuzzy String Matching in SQL Server 2025

EDIT_DISTANCE counts how many single-character changes turn one string into another. SQL Server 2025 adds it, with three relatives, so you can find near-duplicate names without leaving T-SQL. They are a preview feature, and they trip on one setting. This post shows both.

Why Equal Is Not Enough

A customer list collects typos. Jonathan becomes Jonathon, Michael becomes Micheal, and the same person ends up twice. An equals comparison calls them different. LIKE needs you to guess the typo in advance. Fuzzy matching gives each pair a score, and you pick the pairs that score high.

There are four functions. Two come from the edit distance family, and two from the Jaro-Winkler family. Each family has a raw form and a similarity form.

Two Things That Stop You

I ran everything on SQL Server 2025, build 17.0.5005.3. The first block creates the test database and calls the function. A new database has the preview setting off, so SQL Server does not know the function yet.

IF DB_ID(N'SqlFuzzyMatchDemo') IS NULL CREATE DATABASE SqlFuzzyMatchDemo;
GO
USE SqlFuzzyMatchDemo;
GO
SELECT EDIT_DISTANCE(N'Jonathan', N'Jonathon') AS Changes;

Msg 195, Level 15, State 10, Line 1
'EDIT_DISTANCE' is not a recognized built-in function name.

Preview features sit behind a database scoped setting named PREVIEW_FEATURES. I switch it on in the test database only. Then I run the same call again.

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
GO
SELECT EDIT_DISTANCE(N'Jonathan', N'Jonathon') AS Changes;

Msg 9847, Level 16, State 1, Line 1
The fuzzy string matching function does not support SQL_* collations. Use a Windows collation instead.

This is the second stop. A collation is the rule set for comparing and sorting text. The names that start with SQL_ are the old SQL Server collations, and these functions refuse them. My database uses one, as this query shows.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;

The fix is a Windows collation on one of the two values. Put COLLATE Latin1_General_100_CI_AS after it, and the call works.

SELECT EDIT_DISTANCE(N'Jonathan' COLLATE Latin1_General_100_CI_AS, N'Jonathon') AS Changes;

The Four Scores Side by Side

One typo, one identical pair and one unrelated pair show how each score behaves. The Jaro-Winkler distance is cast to three decimals for a short output.

SELECT p.A, p.B, EDIT_DISTANCE(p.A, p.B) AS Changes, EDIT_DISTANCE_SIMILARITY(p.A, p.B) AS EditScore,
    CAST(JARO_WINKLER_DISTANCE(p.A, p.B) AS decimal(4,3)) AS JaroDistance, JARO_WINKLER_SIMILARITY(p.A, p.B) AS JaroScore
FROM (VALUES (N'Jonathan' COLLATE Latin1_General_100_CI_AS, N'Jonathon'), (N'Jonathan', N'Jonathan'), (N'Jonathan', N'Priya')) AS p(A, B);

The edit distance counts work: one letter changed. The similarity form turns that count into a percentage of the string length. One typo in the full name Jonathan Smith scores 93 on that scale, against 88 for Jonathan alone. The Jaro-Winkler forms look at matching letters and give extra credit when the start of both strings matches.

Find the Near Duplicates

Now a customer table. The name column carries the Windows collation itself, so no query needs a COLLATE clause. The data has real typos and a few traps.

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1) PRIMARY KEY,
    FullName nvarchar(60) COLLATE Latin1_General_100_CI_AS NOT NULL,
    City nvarchar(40) NOT NULL
);
INSERT INTO dbo.Customers (FullName, City) VALUES
(N'Jonathan Smith', N'Phoenix'), (N'Jonathon Smith', N'Phoenix'), (N'Jon Smith', N'Seattle'),
(N'Maria Garcia', N'Austin'), (N'Mariah Garcia', N'Austin'),
(N'Priya Patel', N'Denver'), (N'Priya Patil', N'Denver'),
(N'Michael Johnson', N'Boston'), (N'Micheal Johnson', N'Boston'),
(N'Robert Brown', N'Portland'), (N'Roberta Brown', N'Portland'),
(N'Linda Nguyen', N'San Jose'), (N'Smith Jonathan', N'Phoenix');

A self-join compares every name with every other name. The condition a.CustomerID < b.CustomerID keeps each pair once and drops a name compared with itself. I keep pairs with a Jaro-Winkler score of 80 or more.

Mind the cost. Thirteen names make 78 pairs, and 3,000 names make about 4.5 million. Each pair costs a function call. On a real table, narrow the rows first with a cheap filter, such as the same city. Then compare only inside each group.

SELECT a.FullName AS NameA, b.FullName AS NameB, EDIT_DISTANCE(a.FullName, b.FullName) AS Changes,
    EDIT_DISTANCE_SIMILARITY(a.FullName, b.FullName) AS EditScore, JARO_WINKLER_SIMILARITY(a.FullName, b.FullName) AS JaroScore
FROM dbo.Customers AS a
JOIN dbo.Customers AS b ON a.CustomerID < b.CustomerID
WHERE JARO_WINKLER_SIMILARITY(a.FullName, b.FullName) >= 80
ORDER BY JaroScore DESC;

Read the list as suspects, not as verdicts. Michael and Micheal are almost certainly one person. Robert and Roberta, and Maria and Mariah, are different first names that sit one letter apart. A person has to decide those. The last two rows are a short form of the same name, five changes away. A rule of “at most 1 change” would have missed them, and the Jaro-Winkler score caught them.

Search for One Name

The same functions rank candidates for a typed name. A user types “Micheal Jonson” with two typos. The query scores every customer and keeps the best three.

SELECT TOP (3) FullName, EDIT_DISTANCE_SIMILARITY(FullName, N'Micheal Jonson') AS EditScore,
    JARO_WINKLER_SIMILARITY(FullName, N'Micheal Jonson') AS JaroScore
FROM dbo.Customers
ORDER BY EditScore DESC;

The two real candidates come first, far above the third. A big gap like that is the sign of a good match. When the scores fall in a smooth slope, no row is a clear answer.

Case, Accents and Where the Typo Sits

EDIT_DISTANCE follows the collation you give it, so the collation decides what counts as a change. With a case-insensitive collation, jonathan and JONATHAN are the same. With a case-sensitive one, all eight letters differ. Accents work the same way: an accent-insensitive collation treats José and Jose as equal.

SELECT EDIT_DISTANCE(N'jonathan' COLLATE Latin1_General_100_CI_AS, N'JONATHAN') AS CaseInsensitive,
    EDIT_DISTANCE(N'jonathan' COLLATE Latin1_General_100_CS_AS, N'JONATHAN') AS CaseSensitive,
    EDIT_DISTANCE(N'José' COLLATE Latin1_General_100_CI_AI, N'Jose') AS AccentInsensitive,
    EDIT_DISTANCE(N'José' COLLATE Latin1_General_100_CI_AS, N'Jose') AS AccentSensitive;

The two families also disagree about position. A typo in the first letter and a typo in the last letter both cost one change. Jaro-Winkler rewards a matching start, so the first-letter typo scores lower.

SELECT EDIT_DISTANCE(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Konathan Smith') AS ChangesFirst,
    JARO_WINKLER_SIMILARITY(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Konathan Smith') AS JaroFirst,
    EDIT_DISTANCE(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Jonathan Smitk') AS ChangesLast,
    JARO_WINKLER_SIMILARITY(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Jonathan Smitk') AS JaroLast;

What They Cannot Do

Reordered words fool both families. “Jonathan Smith” and “Smith Jonathan” are the same words in a new order. They need 12 changes, and they score 14 on the edit scale and 69 on the Jaro scale. Neither score would flag them.

SELECT EDIT_DISTANCE(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Smith Jonathan') AS Changes,
    EDIT_DISTANCE_SIMILARITY(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Smith Jonathan') AS EditScore,
    JARO_WINKLER_SIMILARITY(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Smith Jonathan') AS JaroScore;

Two small facts help. Swapping two neighboring letters, as in Jonathna, counts as one change, as the last column shows. And EDIT_DISTANCE takes an optional third argument, a limit. With a limit of 2, the pair below returned 3, though the real distance is 5. With limits of 5 and 9, it returned the real 5.

SELECT EDIT_DISTANCE(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Jon Smith', 2) AS Limit2,
    EDIT_DISTANCE(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Jon Smith', 5) AS Limit5,
    EDIT_DISTANCE(N'Jonathan Smith' COLLATE Latin1_General_100_CI_AS, N'Jon Smith', 9) AS Limit9,
    EDIT_DISTANCE(N'Jonathan' COLLATE Latin1_General_100_CI_AS, N'Jonathna') AS Swapped;

So treat any result above your limit as “too far”, not as the true count. Use a limit when you only care about close pairs and do not need the exact number for the rest.

What About SOUNDEX

SOUNDEX turns a name into a four-character sound code. DIFFERENCE compares two codes on a scale of 0 to 4. It says alike or not alike, but it cannot say how alike. It also runs in my database, which has an SQL_ collation.

SELECT DIFFERENCE(N'Jonathan Smith', N'Jonathon Smith') AS Typo, DIFFERENCE(N'Jonathan Smith', N'Jon Smith') AS ShortForm,
    SOUNDEX(N'Jonathan Smith') AS CodeA, SOUNDEX(N'Jonathon Smith') AS CodeB, SOUNDEX(N'Jon Smith') AS CodeC;

The typo gets the same code and the top score. The short form gets a different code and a 3. That is useful, but 3 out of 4 is a blunt number. A score from 0 to 100 tells you more.

The Fair Complaint

You could say that a preview function with a collation limit is not ready for a real customer list. Fair point. Do not merge or delete rows from a score. Use the functions to build a review list that a person reads. Keep them out of nightly jobs until the feature is final.

A Short Checklist

• Turn on PREVIEW_FEATURES in a test database first.
• Give the column, or one argument, a Windows collation.
• Start with a score near 80 and read the pairs by eye before you set a rule.
• A self-join compares every pair, so narrow the rows first, for example by city.
• Give EDIT_DISTANCE a limit when only close pairs matter.
• Treat a high score as a suspect, never as proof.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlFuzzyMatchDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlFuzzyMatchDemo;

EDIT_DISTANCE is not a duplicate remover, it is a list of suspects.

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