Fuzzy string matching

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Changes to SQL introduced in SQL Server 2025 Part two of a five-part series of blogs
In addition to handling AI models, vectors of data and JSON, SQL now includes a number of other new functions and features (as this blog explains) REGEX regular expression functions Fuzzy string matching (this blog) Getting the current date (and not the date/time) Using the new UNISTR function to display Unicode characters Encoding binary files as text for transfer using BASE64_ENCODE

Changes to SQL introduced in SQL Server 2025

Part two of a five-part series of blogs

In addition to handling AI models, vectors of data and JSON, SQL now includes a number of other new functions and features (as this blog explains)

REGEX regular expression functions
Fuzzy string matching (this blog)
Getting the current date (and not the date/time)
Using the new UNISTR function to display Unicode characters
Encoding binary files as text for transfer using BASE64_ENCODE

Posted by Andy Brown on 25 November 2025

In this blog

Preparing to use the new fuzzy matching functions
Using the fuzzy match functions
Using the code for our countries

SQL Server 2025 introduces 4 new functions for testing how similar two strings of text are to each other (I'll explain more about these in the rest of this blog!):

Method	Distance	Similarity
Damerau-Levenshtein	EDIT_DISTANCE	EDIT_DISTANCE_SIMILARITY
Jaro-Winkler	JARO_WINKLER_DISTANCE	JARO_WINKLER_SIMILARITY

I used the EDIT_SIMILARITY function to derive this incredibly useful table showing which countries' names are most and least like each other!

Who knew that Afghanistan would be least like Cabo Verde if you use fuzzy string matching to compare country names?

I'll show the code for how to do this at the bottom of this blog, but we have much to cover before then ...

Preparing to use the new fuzzy matching functions

In order to be able to use the new fuzzy matching functions, you'll need to do two things: have the right compatibility level for your databaase and turn on the appropriate preview feature. You can see the compatibility level of a database by running this query:

SELECT

d.name AS 'Database name',

d.compatibility_level AS 'Compatibility level'

FROM

sys.databases as d

ORDER BY

d.name

I should be OK because my databases have compatibility level 170 or higher:

The database I'll be using is Movies_02, which has compatibility level 170 (showing it was created in SQL Server 2025 or higher).

The fuzzy matching functions are in preview, so you'll need to enable them by running SQL like this:

-- make sure you're in the right database

USE Movies_02;

-- turn this preview feature on for this database

ALTER DATABASE SCOPED CONFIGURATION

SET PREVIEW_FEATURES = ON;

-- just for fun, show this new value

SELECT name, value

FROM sys.database_scoped_configurations

ORDER BY configuration_id desc

-- you could run this at the end of testing

-- to turn the preview feature back off again

ALTER DATABASE SCOPED CONFIGURATION

SET PREVIEW_FEATURES = OFF;

One of the fun features or researching blogs like this is that you discover things you never knew existed. Who knew there were 41 preview features you can turn on in SQL Server 2025?

I've sorted the one we set to the top.

Using the fuzzy match functions

Here is a typical SQL query using all four of the new fuzzy match functions:

-- the two bits of text to compare

DECLARE @first_text varchar(800) = 'John Smith'

DECLARE @second_text varchar(800) = 'Johann Smythe'

SELECT

@first_text as [First text],

@second_text as [Second text],

-- show the distances between the two bits of text

EDIT_DISTANCE(@first_text, @second_text) AS EditDistance,

JARO_WINKLER_DISTANCE(@first_text, @second_text) AS JaroDistance,

EDIT_DISTANCE_SIMILARITY(@first_text, @second_text) AS EditSimilarity,

JARO_WINKLER_SIMILARITY(@first_text, @second_text) AS JaroSimilarity

And here's what this returns:

It turns out that the two bits of text are quite similar - read on.

The first distance measures the number of transformations (4) required to get from the first string of text to the second one, using the Damerau-Levenstein algorithm. These are:

Insert an "a" in the first name.
Insert an "n" in the first name.
Change the "i" to a "y".
Add an "e" at the end.

The second distance uses the Jaro-Winkler method to assess the distance between the two strings of text (a figure of 0 implies an exact match, whereas a figure of 1 means there is no similarity whatsoever). The two similarity figures give a number from 0 (implying no match at all) to 100 (implying an exact match): you can see that the Jaro-Winkler method thinks the two bits of text are closer than does the Damerau-Levenstein one.

Unless you're a maths nerd - a serious one - Wise Owl recommend you not to bother looking up the details of the algorithms: just accept that they give a sensible answer!

Using the code for our countries

I promised I would show the SQL to show for each country the name of the country most and least similar to it. To solve this problem I wrote two separate functions:

CREATE OR ALTER FUNCTION fnMostSimilar(

@CountryName varchar(8000)

)

-- this function returns the name of the country that

-- is most similar to the one we are considering

RETURNS varchar(max)

BEGIN

RETURN (

SELECT TOP 1

c.Country

FROM

Country as c

WHERE

c.Country != @CountryName

ORDER BY

EDIT_DISTANCE_SIMILARITY(

@CountryName,

CAST(c.Country AS varchar(8000))

) DESC

)

END

-- this function returns the name of the country that

-- is least similar to the one we are considering

CREATE OR ALTER FUNCTION fnLeastSimilar(

@CountryName varchar(8000)

)

RETURNS varchar(max)

BEGIN

RETURN (

SELECT TOP 1

c.Country

FROM

Country as c

WHERE

c.Country != @CountryName

ORDER BY

EDIT_DISTANCE_SIMILARITY(

@CountryName,

CAST(c.Country AS varchar(8000))

)

END

-- show the most and least similar country for each

-- row in our table

SELECT

c.Country,

dbo.fnMostSimilar(c.Country) AS 'Most similar',

dbo.fnLeastSimilar(c.Country) AS 'Least similar'

FROM Country AS c

order by c.country

I'm sure there are better and more elegant ways to solve this problem, but the above code works ...

Note that initially I used a data type of varchar(max), but this gives an error when you submit it as an argument to the EDIT_DISTANCE_SIMILARITY function, so I ended up having to use varchar(8000) instead.

Parts of this blog
REGEX regular expression functions Fuzzy string matching (this blog) Getting the current date (and not the date/time) Using the new UNISTR function to display Unicode characters Encoding binary files as text for transfer using BASE64_ENCODE

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

SQL BLOGS

SQL BLOGS

Preparing to use the new fuzzy matching functions

Using the fuzzy match functions

Using the code for our countries

Head office

London

Manchester