EDITDISTANCE

The editDistance(string1, string2) and editDistanceUTF8(string1, string2) functions calculate the minimum number of single-character insertions, deletions, or substitutions required to transform one string into the other, using the Levenshtein edit distance.

Syntax

editDistance(string1, string2)
editDistanceUTF8(string1, string2)

Parameters

Return type

BIGINT UNSIGNED type, indicating the edit distance between string1 and string2.

Behavior description

• editDistance calculates distances in bytes, which is suitable for ASCII or single-byte character sets. For multi-byte character sets (like UTF-8), a single Chinese character is counted as multiple bytes in the differences.
• editDistanceUTF8 first decodes the string into characters (code points) using UTF-8, then calculates the edit distance based on characters (treating each multi-byte character as a single character).
• If any input is NULL, the result is NULL.
• If either input string is an empty string, the result is the length of the other string (in bytes for editDistance and in characters for editDistanceUTF8).
• Each parameter has a maximum length of 65536 (the length of editDistance is in bytes and the length of editDistanceUTF8 is in characters). If the length exceeds this value, an error of the form ERROR 1153 (HY000): Got a packet bigger than 'max_allowed_packet' bytes is returned. The error number is OB_ERR_PARAMETER_TOO_LONG and the error message is string length is too long for edit distance, should be at most 65536.

Examples

1. Enable the ClickHouse extension function mode.

   obclient> ALTER SYSTEM SET sql_func_extension_mode = 'ClickHouse';
   

2. Calculate the edit distance between the strings Base and OceanBase.

   obclient> SELECT editDistance('Base', 'OceanBase') AS d;
   

   The returned result is as follows:

   +------+
   | d    |
   +------+
   |    5 |
   +------+
   1 row in set
   

3. Difference between byte-level and character-level. The editDistance function counts the Chinese character as three UTF-8 bytes, while the editDistanceUTF8 function counts the character as one UTF-8 character.

   obclient> SELECT editDistance('OB', '中OB') AS bytes_diff,
                    editDistanceUTF8('OB', '中OB') AS chars_diff;
   

   The returned result is as follows:

   +------------+------------+
   | bytes_diff | chars_diff |
   +------------+------------+
   |          3 |          1 |
   +------------+------------+
   1 row in set
   

4. NULL and empty string edge cases.

   obclient> SELECT editDistance(NULL, 'abc') AS null_case,
                    editDistance('', 'abc')   AS empty_case;
   

   The result is as follows:

   +-----------+------------+
   | null_case | empty_case |
   +-----------+------------+
   |      NULL |          3 |
   +-----------+------------+
   1 row in set