Edit

EDIT_DISTANCE (Transact-SQL) preview

Applies to: SQL Server 2025 (17.x) Azure SQL Database Azure SQL Managed Instance SQL analytics endpoint in Microsoft Fabric and Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

Note

As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.

Calculates the edit distance between two strings, which is the minimum number of insertions, deletions, substitutions, and transpositions needed to transform one string into the other.

Note

  • EDIT_DISTANCE is in preview.
  • EDIT_DISTANCE is available in SQL Server 2025 (17.x).
  • EDIT_DISTANCE is available in Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy.

Syntax

EDIT_DISTANCE (
    character_expression
    , character_expression [ , maximum_distance ]
)

Arguments

character_expression

An alphanumeric expression of character data. character_expression can be a constant, variable, or column. The character expression can't be of type varchar(max) or nvarchar(max).

maximum_distance

An optional value that specifies the maximum edit distance to calculate. maximum_distance is an integer. When maximum_distance is greater than or equal to 0, the function might stop processing once it determines that the edit distance exceeds the specified value.

If the actual edit distance is less than or equal to maximum_distance, the function returns the actual distance. Otherwise, the function returns maximum_distance + 1.

If maximum_distance isn't specified, or if it's negative, the function returns the actual edit distance. If maximum_distance is NULL, the function returns NULL.

Return value

int

This function implements the Damerau-Levenshtein (Optimal String Alignment) algorithm to return the distance between the two character_expressions, or maximum_distance value if that is smaller.

If any of the inputs is NULL then the function returns a NULL value.

Remarks

If the actual distance is greater than maximum_distance, then the function returns maximum_distance + 1.

Examples

A. Calculate edit distance between two words

The following example compares two words and returns the EDIT_DISTANCE() value as a column, named Distance.

SELECT 'Colour' AS WordUK,
       'Color' AS WordUS,
       EDIT_DISTANCE('Colour', 'Color') AS Distance;

Returns:

WordUK WordUS Distance
------ ------ -----------
Colour Color  1

B. Calculate edit distance between two words limited by a maximum value

The following example compares two words and returns the EDIT_DISTANCE() limited to a maximum value.

SELECT Source,
       Target,
       EDIT_DISTANCE(Source, Target) AS ActualDistance,
       EDIT_DISTANCE(Source, Target, 2) AS LimitedDistance
FROM (VALUES ('Chocolate', 'Sweets')) AS compare(Source, Target);

Here's the result set.

Source    Target    ActualDistance LimitedDistance
--------- --------- -------------- ---------------
Chocolate Sweets    8              3

For more examples, see the EDIT_DISTANCE example in the Fuzzy string matching overview.

Related content

  • Last updated on