November 17th, 2026
heart1 reaction

Fuzzy string matching is now generally available across Microsoft SQL

Senior Product Manager

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:

  • CART and CAT: (1 - 1/4) * 100 = 75.
  • Colour and Color: (1 - 1/6) * 100 is approximately 83.33; the function returns the integer 83.

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:

  1. Postal-code equality creates the shortlist. Only customers within the same postal code are compared.
  2. a.CustomerId < b.CustomerId emits each pair once. It excludes self-comparisons and reversed duplicates such as (2,1) when (1,2) already exists.
  3. The similarity filter flags plausible names. MARTHA and MARHTA pass the illustrative cutoff; pairs involving ROBERT do 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-binary SQL_* collations, such as the common default SQL_Latin1_General_CP1_CI_AS, are not supported. Apply COLLATE to 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_FEATURES database 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.

Learn more

Author

Abhiman Tiwari
Senior Product Manager

Abhiman Tiwari is a Senior Product Manager on the Azure SQL team at Microsoft, primarily focusing on developer experience with Azure SQL. In his previous roles, he has worked as a web developer/ tech-lead, developed several software applications/ products majorly in .NET and related technologies, and done a lot of .NET debugging, reverse engineering, and web application performance tuning.

0 comments