Four native T-SQL functions that tell you how close two strings are, not just whether they’re equal.
MICROSFT isn’t MICROSOFT. MARHTA isn’t MARTHA. INV-1O08 isn’t INV-1008. To an equality check, each pair is simply different. To your business, each is a near miss worth catching.
Today, fuzzy string matching is generally available in Microsoft SQL. EDIT_DISTANCE tells you MICROSFT is one edit from MICROSOFT. JARO_WINKLER_SIMILARITY scores MARTHA and MARHTA at 96 out of 100. It all runs in T-SQL, right where your data lives, with no external libraries, models, or custom code.
Available across all SKUs of:
- Azure SQL Database
- Azure SQL Managed Instance
- SQL database in Microsoft Fabric
- SQL Server 2025, starting with Cumulative Update 11 (CU11), and later cumulative updates
What fuzzy string matching actually means
Fuzzy string matching measures how closely two strings resemble each other without requiring an exact match.
Think of it as comparing the written form of two values. An exact comparison asks, “Are these equal under our comparison rules?” Fuzzy matching adds, “If not, how close are they?”
For an edit-distance algorithm, closeness can be measured by the operations needed to transform one string into the other:
FORM
swap O and R
FROM
One adjacent swap makes the strings identical. Insertion, deletion, substitution, and transposition are transformation operations, not assumptions about what originally caused the difference.
This is useful for typo-tolerant lookup, candidate duplicate detection, master-data reconciliation, and reviewing text captured by optical character recognition (OCR).
It is not the same as understanding meaning. car and automobile are related concepts but have very different character sequences. Nor does a high similarity score establish that two records represent the same person, product, or organization.
Fuzzy matching provides evidence for a decision, not the decision itself.
Meet the four functions
The feature provides two algorithm families, each exposed through a distance function and a similarity function.
| Function | Algorithm | Return type | How to read the result |
|---|---|---|---|
EDIT_DISTANCE |
Damerau-Levenshtein (Optimal String Alignment) | int |
Edit count, or an over-budget marker when limited; lower means closer |
EDIT_DISTANCE_SIMILARITY |
Damerau-Levenshtein (Optimal String Alignment) | int |
Normalized score from 0 to 100; higher means closer |
JARO_WINKLER_DISTANCE |
Jaro-Winkler | float |
Distance from 0 to 1; lower means closer |
JARO_WINKLER_SIMILARITY |
Jaro-Winkler | int |
Score from 0 to 100; higher means closer |
Their signatures are:
EDIT_DISTANCE (
character_expression
, character_expression [ , maximum_distance ]
)
EDIT_DISTANCE_SIMILARITY (
character_expression
, character_expression
)
JARO_WINKLER_DISTANCE (
character_expression
, character_expression
)
JARO_WINKLER_SIMILARITY (
character_expression
, character_expression
)
The inputs can be constants, variables, or columns. Use bounded character types such as varchar(n) or nvarchar(n); varchar(max) and nvarchar(max) are not supported. If an input is NULL, the result is NULL.
A first comparison
Each example is self-contained, creates no permanent objects, and applies a supported collation (Latin1_General_100_CI_AS_SC) with COLLATE, so you can run it without changing your database collation.
;WITH Pair AS (
SELECT
N'Colour' COLLATE Latin1_General_100_CI_AS_SC AS SourceText,
N'Color' COLLATE Latin1_General_100_CI_AS_SC AS TargetText
)
SELECT SourceText, TargetText,
EDIT_DISTANCE(SourceText, TargetText) AS Edits,
EDIT_DISTANCE_SIMILARITY(SourceText, TargetText) AS EditScore,
JARO_WINKLER_DISTANCE(SourceText, TargetText) AS JwDistance,
JARO_WINKLER_SIMILARITY(SourceText, TargetText) AS JwScore
FROM Pair;
The query returns:
| Edits | EditScore | JwDistance, approximately | JwScore |
|---|---|---|---|
| 1 | 83 | 0.033333 | 97 |
One deletion removes the u. Both algorithm families recognize a close match, but they express closeness differently. A Jaro-Winkler distance of approximately 0.033333 is not a fractional number of edits.
Damerau-Levenshtein (Optimal String Alignment): how many edits?
EDIT_DISTANCE and EDIT_DISTANCE_SIMILARITY implement Damerau-Levenshtein (Optimal String Alignment, or OSA), the restricted Damerau-Levenshtein variant.
Each permitted operation has a cost of one:
| Operation | Example | What changes |
|---|---|---|
| Insertion | CT to CAT |
Insert A |
| Deletion | CART to CAT |
Delete R |
| Substitution | CAT to CUT |
Replace A with U |
| Adjacent transposition | FORM to FROM |
Swap O and R |
The distance is the minimum cost of a transformation allowed by this algorithm, so each pair above has a distance of 1.
Why “Optimal String Alignment” matters
The qualification is important: this is not unrestricted Damerau-Levenshtein. In the Optimal String Alignment variant, the same substring cannot be edited more than once along the transformation.
For example, unrestricted Damerau-Levenshtein can transform CA into ABC in two operations:
CA -> AC -> ABC
swap insert B
That sequence re-edits the transposed region. The restricted Damerau-Levenshtein (Optimal String Alignment) algorithm does not allow that transformation path, and the distance is 3, not 2.
SELECT EDIT_DISTANCE(
N'CA' COLLATE Latin1_General_100_CI_AS_SC,
N'ABC' COLLATE Latin1_General_100_CI_AS_SC
) AS Edits;
-- Result: 3
For everyday typos this rarely matters, but it does when you compare results with another library or migrate an existing matching implementation.
From edit count to similarity
An absolute edit count does not tell the whole story. Removing one character from a four-character value changes a larger proportion of the string than removing one character from a six-character value.
EDIT_DISTANCE_SIMILARITY normalizes edit distance using the longer input:
(1 - (edit_distance / greatest(len(string1), len(string2)))) * 100
For example:
CARTandCAT:(1 - 1/4) * 100 = 75.ColourandColor:(1 - 1/6) * 100is approximately83.33; the function returns the integer83.
A score of 83 measures resemblance; it is not an 83% probability that the values refer to the same thing.
Jaro-Winkler: a different way to rank resemblance
Sometimes the question is not “How many edits are allowed?” but “Which names are the most plausible matches?”
Jaro-Winkler considers matching characters within a positional matching window: a character must occur close enough to its position in the other string to count as a match. It also considers the relative order of those matches, then gives additional weight to a shared prefix. It does not calculate the minimum number of operations needed to transform one string into another.
Consider MARTHA and MARHTA. They share the same characters, with T and H out of order, and both start with MAR.
;WITH Names AS (
SELECT NameA COLLATE Latin1_General_100_CI_AS_SC AS NameA,
NameB COLLATE Latin1_General_100_CI_AS_SC AS NameB
FROM (VALUES
(N'MARTHA', N'MARHTA'),
(N'CONTOSO HEALTH', N'CONTOSO RETAIL')
) AS n(NameA, NameB)
)
SELECT NameA, NameB,
EDIT_DISTANCE(NameA, NameB) AS Edits,
JARO_WINKLER_DISTANCE(NameA, NameB) AS JwDistance,
JARO_WINKLER_SIMILARITY(NameA, NameB) AS JwScore
FROM Names
ORDER BY JwScore DESC, NameA, NameB;
| NameA | NameB | Edits | JwDistance, approximately | JwScore |
|---|---|---|---|---|
MARTHA |
MARHTA |
1 | 0.038889 | 96 |
CONTOSO HEALTH |
CONTOSO RETAIL |
5 | 0.082143 | 92 |
Jaro-Winkler is worth evaluating for person and organization names when shared beginnings are meaningful and usually entered correctly. But prefixes can mislead: CONTOSO HEALTH and CONTOSO RETAIL score 92 despite five edits and may be different organizations. The prefix length and scaling factor are fixed; the functions don’t expose parameters for them.
Choose Damerau-Levenshtein (OSA) for an explicit edit budget. Evaluate Jaro-Winkler for ranking character-based resemblance when prefix preference fits your data.
Use maximum_distance when you only need a threshold
Suppose your application accepts candidates within two edits. An exact distance of eight is unnecessary if all you need to know is that the candidate exceeds the budget.
The optional maximum_distance argument lets EDIT_DISTANCE stop early once the distance exceeds the threshold. Within the threshold, it returns the actual distance; beyond it, it returns maximum_distance + 1.
;WITH Pairs AS (
SELECT SourceText COLLATE Latin1_General_100_CI_AS_SC AS SourceText,
TargetText COLLATE Latin1_General_100_CI_AS_SC AS TargetText
FROM (VALUES
(N'CART', N'CAT'),
(N'FORM', N'FROM'),
(N'Chocolate', N'Sweets'),
(N'CAT', N'CAT')
) AS p(SourceText, TargetText)
)
SELECT SourceText, TargetText,
EDIT_DISTANCE(SourceText, TargetText) AS ActualDistance,
EDIT_DISTANCE(SourceText, TargetText, 2) AS BoundedDistance
FROM Pairs
ORDER BY SourceText;
| Pair | ActualDistance | BoundedDistance |
|---|---|---|
CART / CAT |
1 | 1 |
CAT / CAT |
0 | 0 |
Chocolate / Sweets |
8 | 3 |
FORM / FROM |
1 | 1 |
The value 3 for Chocolate / Sweets means “over the budget of two”, not “exactly three edits.” Use the bounded form to accept or reject candidates, not to rank how far apart rejected values are.
Put it to work: typo-tolerant lookup
Let’s return to MICROSFT. We want a short list of catalog values no more than one edit away.
DECLARE @query nvarchar(40) = N'MICROSFT';
;WITH Catalog AS (
SELECT Name COLLATE Latin1_General_100_CI_AS_SC AS Name
FROM (VALUES
(N'MICROSFT'), (N'MICROSOFT'), (N'CONTOSO')
) AS c(Name)
)
SELECT Name,
EDIT_DISTANCE(
Name, @query COLLATE Latin1_General_100_CI_AS_SC
) AS Distance
FROM Catalog
WHERE EDIT_DISTANCE(
Name, @query COLLATE Latin1_General_100_CI_AS_SC, 1
) <= 1
ORDER BY Distance, Name;
The result contains MICROSFT with distance 0, followed by MICROSOFT with distance 1; CONTOSO is outside the budget. The misspelled catalog entry is included deliberately: exact matches rank first, and the application can offer the alternatives instead of silently rewriting the input. The bounded call filters, while the unbounded call supplies the ranking value.
Find candidate duplicates without comparing everyone to everyone
Customer deduplication needs both textual resemblance and business context. Comparing every customer with every other customer produces a number of pairs that grows approximately with the square of the row count.
A practical first step is blocking: group records using a trusted attribute, then compare names within those groups.
;WITH Customer AS (
SELECT CustomerId,
Name COLLATE Latin1_General_100_CI_AS_SC AS Name,
PostalCode
FROM (VALUES
(1, N'MARTHA', N'98052'),
(2, N'MARHTA', N'98052'),
(3, N'MARTHA', N'10001'),
(4, N'ROBERT', N'98052')
) AS c(CustomerId, Name, PostalCode)
)
SELECT a.CustomerId AS CustomerA, b.CustomerId AS CustomerB,
a.Name AS NameA, b.Name AS NameB,
JARO_WINKLER_SIMILARITY(a.Name, b.Name) AS Score
FROM Customer AS a
JOIN Customer AS b
ON a.PostalCode = b.PostalCode
AND a.CustomerId < b.CustomerId
WHERE JARO_WINKLER_SIMILARITY(a.Name, b.Name) >= 92
ORDER BY Score DESC, CustomerA, CustomerB;
Three details make the query useful:
- Postal-code equality creates the shortlist. Only customers within the same postal code are compared.
a.CustomerId < b.CustomerIdemits each pair once. It excludes self-comparisons and reversed duplicates such as(2,1)when(1,2)already exists.- The similarity filter flags plausible names.
MARTHAandMARHTApass the illustrative cutoff; pairs involvingROBERTdo not.
The query returns one pair: customer 1 and customer 2, with a similarity score of 96.
The other MARTHA, in postal code 10001, is not compared with the 98052 group. That illustrates the trade-off: blocking reduces comparisons but can miss true duplicates when the blocking attribute differs or is wrong.
The cutoff of 92 is only illustrative: CONTOSO HEALTH and CONTOSO RETAIL also scored 92 earlier. Confirm candidates with independent evidence, such as a verified email or phone number.
Reconcile OCR mistakes within the right business context
An invoice reader captures INV-1O08, confusing the letter O with the digit 0. Comparing against every invoice would be risky. Start with the vendor, then allow one character edit:
;WITH Captured AS (
SELECT 10 AS VendorId,
N'INV-1O08' COLLATE Latin1_General_100_CI_AS_SC AS Invoice
), ReferenceInvoice AS (
SELECT VendorId,
Invoice COLLATE Latin1_General_100_CI_AS_SC AS Invoice
FROM (VALUES
(10, N'INV-1008'),
(10, N'INV-1208'),
(20, N'INV-1008')
) AS r(VendorId, Invoice)
)
SELECT c.VendorId, c.Invoice AS CapturedInvoice,
r.Invoice AS CandidateInvoice,
EDIT_DISTANCE(c.Invoice, r.Invoice) AS Edits
FROM Captured AS c
JOIN ReferenceInvoice AS r ON c.VendorId = r.VendorId
WHERE EDIT_DISTANCE(c.Invoice, r.Invoice, 1) <= 1
ORDER BY c.VendorId, r.Invoice;
The query returns two candidates for vendor 10:
| Candidate invoice | Transformation | Edits |
|---|---|---|
INV-1008 |
Replace O with 0 |
1 |
INV-1208 |
Replace O with 2 |
1 |
Vendor 20’s invoice is excluded by the vendor condition. Within vendor 10, both substitutions cost one edit; the function doesn’t know that OCR often confuses O and 0. Attributes such as amount and date must decide which invoice was intended.
Useful with AI, without being an AI model
These functions require no embeddings, model training, or inference endpoint. They also fit naturally into AI-assisted workflows.
For example, a language model might extract CONTOS from a document. The application can use fuzzy matching to shortlist names in its organization directory. The same SQL works if the input came from a person or a batch import instead.
DECLARE @entity nvarchar(100) = N'CONTOS';
;WITH Organization AS (
SELECT OrganizationId,
Name COLLATE Latin1_General_100_CI_AS_SC AS Name,
CountryCode
FROM (VALUES
(1, N'CONTOSO', N'US'),
(2, N'CONTOSO HEALTH', N'US'),
(3, N'CONTOSO RETAIL', N'US'),
(4, N'CONTOSO', N'GB')
) AS o(OrganizationId, Name, CountryCode)
)
SELECT TOP (5) OrganizationId, Name,
JARO_WINKLER_SIMILARITY(
Name, @entity COLLATE Latin1_General_100_CI_AS_SC
) AS Score
FROM Organization
WHERE CountryCode = N'US'
AND JARO_WINKLER_SIMILARITY(
Name, @entity COLLATE Latin1_General_100_CI_AS_SC
) >= 80
ORDER BY Score DESC, OrganizationId;
The query returns three US candidates: CONTOSO with a score of 97, followed by CONTOSO HEALTH and CONTOSO RETAIL at 89 each. The GB record is excluded. Change @entity to N'ZZZZ' and the query returns no rows; the application should treat that as “no match” rather than force a best guess.
Fuzzy matching won’t expand an acronym into a company name or resolve related meanings. Use an alias table or a semantic approach for those tasks.
Where this fits in the SQL matching toolbox
Fuzzy string matching extends existing capabilities; it does not replace them.
| Question | Appropriate tool | Example |
|---|---|---|
| Are these values equal under the intended comparison rules? | Equality, joins, and an appropriate collation | Match a trusted customer key |
| Does the text contain a known pattern or substring? | LIKE, CHARINDEX, PATINDEX |
Find a known prefix |
| Does the text follow a structured pattern? | REGEXP_* functions |
Validate or extract a formatted value |
| Do these values have similar phonetic representations? | SOUNDEX, DIFFERENCE |
Coarse screening of English-oriented names |
| How far apart are these values in character edits? | EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY |
Measure edit count or normalized edit similarity |
| How similar are character alignment and prefixes? | JARO_WINKLER_DISTANCE, JARO_WINKLER_SIMILARITY |
Rank plausible name matches |
| Which documents contain relevant words or phrases? | Full-text search | Linguistic document retrieval |
| Which representations are close in embedding space? | Vector similarity/search | Semantic retrieval for AI applications |
For example, SOUNDEX('Robert') and SOUNDEX('Rupert') both return R163: a phonetic signal, which is different from the character-similarity scores announced here.
Good to know
- Collation: The functions support Windows collations and binary (
BIN,BIN2) collations. Non-binarySQL_*collations, such as the common defaultSQL_Latin1_General_CP1_CI_AS, are not supported. ApplyCOLLATEto the inputs, as the examples do; you don’t need to change the database collation. - Scale: Narrow the candidate set with trusted business keys, such as region, vendor, or category, before scoring.
- Scores: Test thresholds on your own labeled examples. A score is evidence of a possible match, not proof.
Get started
- Azure SQL Database and SQL database in Microsoft Fabric: The functions are available now.
- Azure SQL Managed Instance: Use the SQL Server 2025 or Always-up-to-date update policy.
- SQL Server 2025: Install CU11 or later. If you tried the functions in preview, you no longer need the
PREVIEW_FEATURESdatabase scoped configuration to use them.
Pick a column you already struggle to match, try the examples above on your own data, and see what turns up. Whether you’re cleaning customer records, forgiving a typo in a search box, or grounding an AI-extracted name, fuzzy string matching is now a native part of T-SQL, ready wherever your data lives.
We’d love to hear what you build with it.
0 comments
Be the first to start the discussion.