Skip to content

edit-distance-transact-sql.md

Latest commit

 

History

History
81 lines (57 loc) · 2.86 KB

edit-distance-transact-sql.md

File metadata and controls

81 lines (57 loc) · 2.86 KB
title EDIT_DISTANCE (Transact-SQL)
description EDIT_DISTANCE calculates the number of insertions, deletions, substitutions, and transpositions needed to transform one string to another.
author MikeRayMSFT
ms.author mikeray
ms.reviewer abhtiwar, wiassaf, randolphwest
ms.date 05/19/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
dev_langs
TSQL
monikerRange =azuresqldb-current || =azuresqldb-mi-current || =fabric || >=sql-server-2016
ms.custom
build-2025

EDIT_DISTANCE (Transact-SQL) preview

[!INCLUDE sqlserver2025-asdb-asmi-fabricsqldb]

[!INCLUDE preview]

Calculates the number of insertions, deletions, substitutions, and transpositions needed to transform one string to another.

Note

  • EDIT_DISTANCE is currently in preview.
  • SQL Server support for EDIT_DISTANCE introduced in [!INCLUDE sssql25-md].

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 cannot be of type varchar(max) or nvarchar(max).

maximum_distance

The maximum distance that should be computed. Integer. If greater than or equal to zero, then the function returns the actual distance value or a distance value that is greater than maxiumum_distance value. If the actual distance is greater than maximum_distance, then the function may return a value greater than or equal to maximum_distance. If the parameter is not specified or if maximum_distance is negative, then the function returns the actual number of transformations needed. If the value is NULL, then the function returns NULL.

Return value

int

Remarks

This function implements the Damerau-Levenshtein algorithm. If any of the inputs is NULL then the function returns a NULL value. Otherwise, the function returns an integer value from 0 to the number of transformations or maximum_distance value.

Example

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

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

Returns:

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

For additional examples, see Example EDIT_DISTANCE().

Related content