The editDistance(string1, string2) and editDistanceUTF8(string1, string2) functions calculate the Levenshtein edit distance between two strings. The Levenshtein edit distance is the minimum number of operations required to transform one string into another by performing insertions, deletions, or substitutions at most once.
Note
This is a ClickHouse-compatible function. It is disabled by default. To use it, set the tenant-level parameter sql_func_extension_mode to ClickHouse. For more information, see sql_func_extension_mode.
Syntax
editDistance(string1, string2)
editDistanceUTF8(string1, string2)
Parameters
Parameters |
Note |
|---|---|
string1, string2 |
Two strings to be compared. |
Return type
BIGINT UNSIGNED type, which represents the Levenshtein distance between string1 and string2.
Behavior description
editDistancecalculates the distance in bytes, making it suitable for ASCII or single-byte character sets. For multibyte character sets (such as UTF-8), a single Chinese character may be counted as multiple bytes of difference.editDistanceUTF8: Decodes the text to Unicode characters first, then calculates the distance based on the Unicode characters. A multi-byte character counts as one character in the edit distance.- If any input parameter is
NULL, the result isNULL. - If any input string is empty, the result is the length of the other string (
editDistancemeasures bytes,editDistanceUTF8measures characters). - The maximum length of each input parameter is 65,536 (
editDistanceis in bytes,editDistanceUTF8is in characters). Exceeding this limit will result in the errorERROR 1153 (HY000): Got a packet bigger than 'max_allowed_packet' bytes, corresponding to the kernel error codeOB_ERR_PARAMETER_TOO_LONG, with the messagestring length is too long for edit distance, should be at most 65536.
Examples
Enable the ClickHouse extension function mode.
obclient> ALTER SYSTEM SET sql_func_extension_mode = 'ClickHouse';Calculate the Levenshtein distance between the strings
BaseandOceanBase.obclient> SELECT editDistance('Base', 'OceanBase') AS d;The return result is as follows:
+------+ | d | +------+ | 5 | +------+ 1 row in setThe difference between byte-level and character-level comparison.
editDistancecounts "middle" as three UTF-8 bytes for the distance, whileeditDistanceUTF8counts it as one character.obclient> SELECT editDistance('OB', 'China OB') AS bytes_diff, editDistanceUTF8('OB', 'Chinese OB') AS chars_diff;The return result is as follows:
+------------+------------+ | bytes_diff | chars_diff | +------------+------------+ | 3 | 1 | +------------+------------+ 1 row in setBoundary cases involving
NULLand empty strings.obclient> SELECT editDistance(NULL, 'abc') AS null_case, editDistance('', 'abc') AS empty_case;The return result is as follows:
+-----------+------------+ | null_case | empty_case | +-----------+------------+ | NULL | 3 | +-----------+------------+ 1 row in set