Fuzzy String Matching in SQL Server 2025

October 8, 2025 — Leonard Lobel

Fuzzy string matching is the process of finding strings that are approximately equal, rather than exact matches. This is a critical capability for data cleansing, deduplication, primitive natural language search, and matching user input against known values.

Until now, SQL Server’s fuzzy options have been limited to phonetic comparisons. But now, SQL Server 2025 (and Azure SQL Database) introduces a set of modern string similarity functions you can run directly in T-SQL with far more precision.

Note: This post is based on SQL Server 2025 CTP 2.1. Syntax and behavior are subject to subtle changes by the time the product is released. Fuzzy string matching will ultimately be supported across all SKUs of SQL Server, including SQL Server 2025 for Windows, SQL Server 2025 for Linux, Azure SQL Database, and Managed Instance.

Legacy Options: SOUNDEX and DIFFERENCE

These two functions have been around for decades:

  • SOUNDEX: Produces a code representing the phonetic sound of a word (e.g., “Green” → G650, “Greener” → G656).
  • DIFFERENCE: Compares SOUNDEX codes on a 1–4 scale (4 ≈ exact).

While these work marginally well for quick phonetic checks, they are not suited for long strings or nuanced comparisons.

New in SQL Server 2025: Four Modern Fuzzy Matching Functions

SQL Server 2025 now has these well-known fuzzy matching algorithms built into the engine:

Function NameReturnsUse Cases
EDIT_DISTANCENumber of edits (insert/delete/substitute) between two strings (aka Levenshtein distance)You want a raw “how many changes?” counter or to sort by degree of change
EDIT_DISTANCE_SIMILARITYNormalized similarity percentage (0–100) (aka Levenshtein similarity)You want a rankable score or thresholds like >= 80
JARO_WINKLER_DISTANCEA distance score optimized for short strings (lower is better)You prefer inverse scoring (distance)
JARO_WINKLER_SIMILARITYA similarity score emphasizing common prefixes (higher is better)Names and short, typo-prone values

Exploring with Word Pairs

Let’s create a table of word pairs and score them with both the new and legacy functions.

CREATE TABLE WordPair (
  WordPairId    int IDENTITY PRIMARY KEY,
  Word1         varchar(50),
  Word2         varchar(50)
)

Now populate the table with some sample data:

INSERT INTO WordPair VALUES
 ('Colour',     'Color'),
 ('Flavour',    'Flavor'),
 ('Centre',     'Center'),
 ('Theatre',    'Theater'),
 ('Theatre',    'Theatrics'),
 ('Theatre',    'Theatrical'),
 ('Organise',   'Organize'),
 ('Analyse',    'Analyze'),
 ('Catalogue',  'Catalog'),
 ('Programme',  'Program'),
 ('Metre',      'Meter'),
 ('Honour',     'Honor'),
 ('Neighbour',  'Neighbor'),
 ('Travelling', 'Traveling'),
 ('Grey',       'Gray'),
 ('Green',      'Greene'),
 ('Green',      'Greener'),
 ('Green',      'Greenery'),
 ('Green',      'Greenest'),
 ('Orange',     'Purple'),      -- very different
 ('Defence',    'Defense'),
 ('Practise',   'Practice'),
 ('Practice',   'Practice'),    -- identical
 ('Aluminium',  'Aluminum'),
 ('Cheque',     'Check')

This sample include a mix of near-matches (British vs. American spellings), slight variations (“Green” and “Greener”), one intentionally exact pair (“Practice” and “Practice”), and one intentionally bad pair (“Orange” vs. “Purple”).

Now let’s score each pair using the new and legacy functions:

SELECT
  *,
  -- New SQL Server 2025 fuzzy matching functions
  LevenshteinDistance     = EDIT_DISTANCE(Word1, Word2),
  LevenshteinSimilarity   = EDIT_DISTANCE_SIMILARITY(Word1, Word2),
  JaroWrinklerDistance    = JARO_WINKLER_DISTANCE(Word1, Word2),
  JaroWrinklerSimilarity  = JARO_WINKLER_SIMILARITY(Word1, Word2),
  -- Legacy SQL Server fuzzy matching functions
  Soundex1                = SOUNDEX(Word1),
  Soundex2                = SOUNDEX(Word2),
  Difference              = DIFFERENCE(Word1, Word2)
FROM
  WordPair
ORDER BY 
  LevenshteinSimilarity DESC

This query exercises the four new functions alongside the two legacy functions for every row. The results are sorted by EDIT_DISTANCE_SIMILARITY descending (0–100; 100 = exact), which yields a quick and easy comparison.

Run the query, and observe:

  • The exact match on “Practice/Practice” floats to the very top (similarity 100, distance 0).
  • Close pairs like “Grey/Gray” and “Colour/Color” cluster near the top with high similarity and small edit distances.
  • The outlier (“Orange/Purple”) drops to the bottom with low similarity and large distance.
  • You’ll see that legacy SOUNDEX/DIFFERENCE sometimes overrate or underrate certain pairs, while the newer metrics capture nuance (insertions, substitutions, transpositions, prefix weight).

Clean up the example table

DROP TABLE WordPair

Real-World Deduping (Customer Records)

Now let’s detect potential duplicates in a customer table by scoring first names, last names, and addresses, then combining them.

CREATE TABLE Customer (
  CustomerId int IDENTITY PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50),
  Address varchar(100)
)

While minimal, this structure will suffice to keep focus on fuzzy matching. Now populate the table with sample data, including a mix of exact duplicate and near duplicate values across the first name, last name, and address fields.

INSERT INTO Customer VALUES
 ('Johnathan',  'Smith',    '123 North Main Street'),
 ('Jonathan',   'Smith',    '123 N Main St.'),
 ('Johnathan',  'Smith',    '456 Ocean View Blvd'),
 ('Johnathan',  'Smith',    '123 North Main Street'),
 ('Daniel',     'Smith',    '123 N. Main St.'),
 ('Danny',      'Smith',    '123 N. Main St.'),
 ('John',       'Smith',    '123 Main Street'),
 ('Jonathon',   'Smyth',    '123 N Main St'),
 ('Jon',        'Smith',    '123 N Main St.'),
 ('Johnny',     'Smith',    '124 N Main St'),
 ('Ethan',      'Goldberg', '742 Evergreen Terrace'),
 ('Carlos',     'Rivera',   '456 Ocean View Boulevard'),
 ('Carlos',     'Rivera',   '456 Ocean View Blvd'),
 ('Carl',       'Rivera',   '456 Ocean View Boulevard'),
 ('Carlos',     'Rivera',   '456 Ocean View Boulevard')

SELECT * FROM Customer

Score First Name Similarity

We’ll compute pairwise first-name similarity across all row combinations using JARO_WINKLER_SIMILARITY (great for short strings and prefix sensitivity).

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    FirstName1          = c1.FirstName,
    FirstName2          = c2.FirstName,
    FirstNameSimilarity = JARO_WINKLER_SIMILARITY(c1.FirstName, c2.FirstName)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  FirstNameQuality = CASE
    WHEN FirstNameSimilarity = 1    THEN 'Exact'
    WHEN FirstNameSimilarity >= .85 THEN 'Very Strong'
    WHEN FirstNameSimilarity >= .75 THEN 'Strong'
    WHEN FirstNameSimilarity >= .4  THEN 'Weak'
                                    ELSE 'Very Weak'
    END
FROM
  PairwiseSimilarityCte
ORDER BY
  FirstNameSimilarity DESC

How it works:

  • The self-join c2.CustomerId < c1.CustomerId generates unique unordered pairs (no duplicates, no self-pairs).
  • The CTE computes similarity for each pair; the outer query labels it with human-friendly buckets.

What to expect:

  • Exact repeats (e.g., identical first names) show FirstNameSimilarity = 1 and label Exact.
  • Variants like Johnathan/ Jonathan and Jon/ John cluster as Very Strong or Strong.
  • Unrelated names (e.g., Daniel / Johnathan) drop into Weak/Very Weak.

Score Last Name Similarity

Same pattern, now focused on last names. This will show phonetic look-alikes like Smith/Smyth scoring high.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    LastName1           = c1.LastName,
    LastName2           = c2.LastName,
    LastNameSimilarity  = JARO_WINKLER_SIMILARITY(c1.LastName, c2.LastName)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  LastNameQuality = CASE
    WHEN LastNameSimilarity = 1     THEN 'Exact'
    WHEN LastNameSimilarity >= .85  THEN 'Very Strong'
    WHEN LastNameSimilarity >= .75  THEN 'Strong'
    WHEN LastNameSimilarity >= .4   THEN 'Weak'
                                    ELSE 'Very Weak'
    END
FROM
    PairwiseSimilarityCte
ORDER BY
    LastNameSimilarity DESC

What to expect:

  • All Smith/Smith pairs rank Exact.
  • Smith/Smyth should score Very Strong (tiny spelling change).
  • Completely different surnames (e.g., Goldberg / Smith) fall low.

Score Address Similarity

Addresses are noisy (abbreviations, punctuation, ordering). JARO_WINKLER_SIMILARITY handles short transpositions and prefix overlaps well.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    Address1            = c1.Address,
    Address2            = c2.Address,
    AddressSimilarity   = JARO_WINKLER_SIMILARITY(c1.Address, c2.Address)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  AddressQuality = CASE
    WHEN AddressSimilarity = 1      THEN 'Exact'
    WHEN AddressSimilarity >= .85   THEN 'Very Strong'
    WHEN AddressSimilarity >= .75   THEN 'Strong'
    WHEN AddressSimilarity >= .4    THEN 'Weak'
                                    ELSE 'Very Weak'
  END
FROM
  PairwiseSimilarityCte
ORDER BY
  AddressSimilarity DESC

What to expect:

  • Exact duplicates top the list.
  • Variants like “123 North Main Street” vs “123 N Main St.” score Very Strong/Strong.
  • Unrelated addresses (e.g., “742 Evergreen Terrace” vs “123 N. Main St.”) land low.

Combine All Three: A Composite Match Score

A high first-name score plus a low address score probably isn’t a true duplicate. Let’s average the three similarities into a FinalCombinedScore for better overall ranking.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    FirstName1          = c1.FirstName,
    FirstName2          = c2.FirstName,
    FirstNameSimilarity = JARO_WINKLER_SIMILARITY(c1.FirstName, c2.FirstName),
    LastName1           = c1.LastName,
    LastName2           = c2.LastName,
    LastNameSimilarity  = JARO_WINKLER_SIMILARITY(c1.LastName, c2.LastName),
    Address1            = c1.Address,
    Address2            = c2.Address,
    AddressSimilarity   = JARO_WINKLER_SIMILARITY(c1.Address, c2.Address)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
),
FinalCombinedScoreCte AS (
  SELECT
    *,
    FinalCombinedScore = (FirstNameSimilarity + LastNameSimilarity + AddressSimilarity) / 3.0
  FROM
    PairwiseSimilarityCte
)
SELECT
  *,
  FinalQuality = CASE
    WHEN FinalCombinedScore = 1     THEN 'Exact'
    WHEN FinalCombinedScore >= .85  THEN 'Very Strong'
    WHEN FinalCombinedScore >= .75  THEN 'Strong'
    WHEN FinalCombinedScore >= .4   THEN 'Weak'
                                    ELSE 'Very Weak'
  END
FROM
  FinalCombinedScoreCte
ORDER BY
  FinalCombinedScore DESC

How it works:

  • The first CTE computes three similarities for each pair.
  • The second CTE averages them into FinalCombinedScore.
  • The outer query labels and sorts by that combined metric.

What to expect:

  • True duplicates (identical first, last, address) appear right at the top (Exact).
  • Pairs like “Jonathon Smyth, 123 N Main St” and “Johnathan Smith, 123 North Main Street” rank high because names and addresses are all very similar.
  • Pairs that share a name but differ in address (e.g., Johnathan Smith at 456 Ocean View Blvd vs 123 North Main) rank lower—exactly what you want when deduping.

    Final Thoughts

    The new fuzzy string functions in SQL Server 2025 finally give T-SQL first-class similarity scoring. Whether you’re cleaning legacy data, merging customer lists, or building typo-tolerant features, you can now do it natively, efficiently, and with the granularity you always wanted.

    Posted in SQL Server, SQL Server 2025. Tags: , . Leave a Comment »

    Leave a comment

    «

    »