Fuzzy string matching (SQL Server 2025)

In one line: SQL Server 2025’s EDIT_DISTANCE and JARO_WINKLER_SIMILARITY find “Jhonson” when you search for “Johnson”, but they compute every row, their two similarity scores disagree, they refuse the SQL_* collations many servers use by default, and before CU10 they also needed preview features.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/fuzzy-string-matching on GitHub.

What you’ll see

  • Error 195, “‘EDIT_DISTANCE’ is not a recognized built-in function name”, until PREVIEW_FEATURES is on in the database. That was on RTM-CU9-GDR; on CU10 (17.0.5035.5) the functions ran without it.
  • Error 9847, “The fuzzy string matching function does not support SQL_* collations”, on columns in SQL_Latin1_General_CP1_CI_AS.
  • A threshold that behaves differently between functions. Jonathan/Jon scored 85 with Jaro-Winkler and 38 with edit-distance similarity.
  • A name search that reads the whole table, every time.

Why it happens

A distance is computed between two specific strings, so no index can find “everything within two edits”. Every row is compared.

The two scores measure different things. Edit distance counts insertions, deletions, substitutions and swaps. Jaro-Winkler weighs matching characters and rewards a shared beginning, so a short prefix of a long name scores high.

SOUNDEX, the old way, encodes how a name sounds but keeps its first letter, so Catherine and Katherine never match. And it gives Robert and Rupert the same code.

What to do

  • Narrow the rows first with something indexed: a SOUNDEX code, the first letters, a postcode. In the demo that cut the CPU from 149 ms to 20 ms. Know what it misses: it lost Kohnson.
  • Pick one score and tune its threshold on your own data. Two edits of a seven-letter name also matched Roinson, a misspelt Robinson.
  • Cast to a Windows collation (COLLATE Latin1_General_100_CI_AS) when the column uses a SQL_* collation.
  • Use the maximum-distance argument to stop early: EDIT_DISTANCE(a, b, 2) returns 3 for anything further than 2.

What doesn’t help

  • One threshold for both scores. They’re both 0 to 100 and don’t agree.
  • SOUNDEX alone. It misses a different first letter and matches different names.

Check your server

-- Collation of every database (the fuzzy functions refuse SQL_* collations),
-- and whether preview features are on in this one (SQL Server 2025+).
SELECT name, collation_name,
       CASE WHEN collation_name LIKE 'SQL[_]%' THEN 'SQL collation: cast first'
            ELSE 'Windows collation' END AS fuzzy_functions
FROM sys.databases
ORDER BY name;

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'PREVIEW_FEATURES';

See it happen

The fuzzy-string-matching demo builds 200,000 surnames with four kinds of typo. It shows what the functions need, what EDIT_DISTANCE counts, where the two similarity scores disagree, and how SOUNDEX compares. Then it searches every row, and searches SOUNDEX-first. On 2022 and earlier it reports SKIPPED.

Related: regular expressions · non-SARGable predicates · data type traps

Measured on SQL Server 2025 RTM-CU9-GDR (17.0.5015.4), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights