LEVENSHTEIN

Compute the minimum single-character edit distance between two strings.

Category: stringReturns: INTEGERDialect: PostgreSql

Syntax

LEVENSHTEIN(str1, str2)

Description

## Overview Computes the Levenshtein edit distance between two strings, defined as the minimum number of single-character insertions, deletions, or substitutions required to transform one string into the other. A return value of 0 means the strings are identical. Levenshtein is a classic fuzzy-matching signal for spelling correction, deduplication of free-text fields, and approximate joins. It has O(n*m) cost where n and m are the two string lengths, so it is only practical for short inputs or when a blocking step (for example a Soundex prefilter) has already narrowed the candidate set. ## Behavior - Returns NULL when either argument is NULL. - Returns 0 when the two strings are character-for-character identical. - Returns LENGTH(other) when one of the inputs is an empty string. - Costs are equal: insertion, deletion, and substitution each count as 1. There is no special cost for transpositions (use a Damerau-Levenshtein variant if that matters). - Comparison is case-sensitive. LOWER both sides for case-insensitive distance. - Operates on Unicode code points; multi-byte UTF-8 characters count as one edit. - Cost is O(n*m) in the product of the two string lengths. ## Compatibility - Matches the classic Levenshtein distance definition. - Does not compute Damerau-Levenshtein (which treats a transposition as cost 1).

Parameters

Name	Type	Description
`str1`		Specifies the first string for distance computation.
`str2`		Specifies the second string for distance computation.

Examples

-- Identical strings
SELECT LEVENSHTEIN('hello', 'hello');  -- 0

-- Single substitution
SELECT LEVENSHTEIN('kitten', 'sitten');  -- 1

-- Multiple edits (substitute + delete + insert)
SELECT LEVENSHTEIN('kitten', 'sitting');  -- 3

-- Against empty string, distance equals length of the other
SELECT LEVENSHTEIN('', 'abc');  -- 3

-- Case-sensitive
SELECT LEVENSHTEIN('ABC', 'abc');  -- 3

-- NULL propagates
SELECT LEVENSHTEIN(CAST(NULL AS VARCHAR), 'abc');  -- NULL

Pitfalls

Levenshtein is O(n*m). On long strings it becomes expensive quickly. Prefilter candidates with a cheap signal (SOUNDEX, trigram index, prefix filter) before running LEVENSHTEIN.
LEVENSHTEIN is case-sensitive. LOWER both arguments when comparing human-entered text.
LEVENSHTEIN treats a transposition (swap of adjacent characters) as two edits (delete + insert). If you need transposition-aware matching, use a Damerau variant.
Very small distances are not meaningful for very short inputs. LEVENSHTEIN('ab', 'cd') is 2, which is 'everything changed' despite being a small absolute number.