Model / similarity · All operations

Measure the distance between two values

A variant of Find similar rows

Compute a distance, similarity or inner product between two vectors or strings in the same row.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Measure the distance, similarity and inner product of two vectors or two strings in a row

SQL

CREATE TEMP TABLE vec_pairs AS SELECT * FROM (VALUES
  ('a', [1.0, 2.0, 2.0]::DOUBLE[3], [2.0, 0.0, 1.0]::DOUBLE[3]),
  ('b', [1.0, 0.0, 0.0]::DOUBLE[3], [0.0, 1.0, 0.0]::DOUBLE[3]),
  ('c', [1.0, 1.0, 1.0]::DOUBLE[3], [2.0, 2.0, 2.0]::DOUBLE[3])) t(id, u, v);
CREATE TEMP TABLE vectors AS
SELECT id, array_distance(u, v) AS euclidean, array_cosine_similarity(u, v) AS cosine_similarity, array_cosine_distance(u, v) AS cosine_distance, array_inner_product(u, v) AS inner_product FROM vec_pairs;
CREATE TEMP TABLE str_pairs AS SELECT * FROM (VALUES ('kitten', 'sitting'), ('martha', 'marhta'), ('karolin', 'kathrin'), ('same', 'same')) t(s, t);
CREATE TEMP TABLE strings AS
SELECT s, t, levenshtein(s, t) AS edits, jaro_similarity(s, t) AS jaro, jaro_winkler_similarity(s, t) AS jaro_winkler, CASE WHEN length(s) = length(t) THEN hamming(s, t) END AS hamming FROM str_pairs;
SELECT * FROM vectors ORDER BY id;
SELECT * FROM strings ORDER BY s;
-- the values are worked by hand: (1,2,2) and (2,0,1) are sqrt(6) apart with inner product 4 and cosine 4 / (3 * sqrt(5));
-- kitten to sitting takes 3 edits, and martha and marhta score 0.9444 by Jaro and 0.9611 by Jaro-Winkler (Winkler 1990)
SELECT CASE WHEN ((SELECT abs(euclidean - sqrt(6)) < 1e-9 AND abs(cosine_similarity - 4 / (3 * sqrt(5))) < 1e-9 AND abs(cosine_distance - (1 - 4 / (3 * sqrt(5)))) < 1e-9 AND inner_product = 4 FROM vectors WHERE id = 'a')
  AND (SELECT euclidean = sqrt(2) AND cosine_similarity = 0 AND inner_product = 0 FROM vectors WHERE id = 'b')
  AND (SELECT abs(euclidean - sqrt(3)) < 1e-9 AND abs(cosine_similarity - 1) < 1e-9 AND inner_product = 6 FROM vectors WHERE id = 'c')
  AND (SELECT edits = 3 AND hamming IS NULL FROM strings WHERE s = 'kitten')
  AND (SELECT edits = 2 AND abs(jaro - 0.9444444444) < 1e-9 AND abs(jaro_winkler - 0.9611111111) < 1e-9 FROM strings WHERE s = 'martha')
  AND (SELECT hamming = 3 FROM strings WHERE s = 'karolin')
  AND (SELECT edits = 0 AND jaro = 1 AND hamming = 0 FROM strings WHERE s = 'same')) THEN 'as expected' ELSE error('the vector and string measures should match the values worked by hand for each of the seven pairs') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the vector and string measures should match the values worked by hand for each of the seven pairs”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache SeaTunnel (1)

Vector Functionsread 2026-09-27
8 functions: VECTOR_DIMS, VECTOR_NORM, INNER_PRODUCT, COSINE_DISTANCE, L1_DISTANCE, …

Ibis (1)

Levenshteinread 2026-09-27
Edit distance between two strings

mlpack (2)

Distancesread 2026-09-27
Distance metrics for the geometric algorithms
Kernelsread 2026-09-27
Mercer kernels for the kernel-based algorithms

subtoken (10)

array_cosine_distanceread 2026-09-27
Cosine distance of two fixed-size arrays of equal length; an HNSW index can serve it
array_cosine_similarityread 2026-09-27
Cosine similarity of two fixed-size arrays of equal length
array_distanceread 2026-09-27
Euclidean distance of two fixed-size arrays of equal length; an HNSW index can serve it
array_inner_productread 2026-09-27
Inner product of two fixed-size arrays of equal length
array_negative_inner_productread 2026-09-27
Negative inner product of two fixed-size arrays; an HNSW index can serve it
list_cosine_distanceread 2026-09-27
Cosine distance of two same-sized lists
list_cosine_similarityread 2026-09-27
Cosine similarity of two same-sized lists; works on subtoken's FLOAT[] with no cast
list_distanceread 2026-09-27
Euclidean distance of two same-sized lists
list_inner_productread 2026-09-27
Inner product of two same-sized lists
list_negative_inner_productread 2026-09-27
Negative inner product of two same-sized lists

Background

A similarity measure is a real-valued function that quantifies the similarity between two objects.^[1] Similarity measures are usually in some sense the inverse of distance metrics, taking large values for similar objects.^[1] A distance can sometimes be derived from a similarity s as one minus s.^[1]

When to use it

Different types of similarity measure exist for different types of objects, depending on the objects being compared.^[1] Measures of similarity between two real-valued vectors include the Euclidean, Manhattan, Minkowski and Chebyshev distances.^[1] Cosine similarity, a commonly used similarity measure for real-valued vectors, is used in information retrieval to score the similarity of documents.^[1] Edit distance, which counts the minimum number of operations needed to turn one string into another, is frequently used in natural language processing, as in spell-checking.^[1]^[2] Jaro distance, another measure of string similarity, is commonly used in record linkage to compare first and last names with other sources.^[1] In record linkage and deduplication, edit distances can be used to compare names, addresses and other text fields that may contain spelling variation or transcription errors.^[3]

In DuckDB, the text similarity functions treat characters of different cases, such as a and A, as different.^[4] Lower-case and trim both strings before measuring them, unless a difference in case is itself a difference you care about. (Meridian's view) DuckDB's hamming function, which counts the positions with different characters in two strings, requires both strings to be of equal length.^[4] DuckDB's array distance and similarity functions take two arrays of the same size, whose elements cannot be NULL.^[5] Decide how a row with a missing value should score before the measure runs, rather than finding out from an error or a NULL. (Meridian's view)

Cosine distance, often defined as one minus the cosine similarity, is not a true distance metric, because it does not satisfy the triangle inequality.^[6] The Jaro–Winkler distance is not a metric in the mathematical sense, because it does not obey the triangle inequality.^[7] Check that a measure is a true metric before relying on a method that assumes one. (Meridian's view)

The method

The Euclidean distance between two points is the length of the line segment between them, calculated from their Cartesian coordinates with the Pythagorean theorem.^[8] In some applications in statistics and optimisation, the square of the Euclidean distance is used instead of the distance itself.^[8]

Cosine similarity is the dot product of two vectors divided by the product of their lengths, so it depends on their angle and not on their magnitudes.^[6] Cosine similarity ranges from −1 for vectors pointing in opposite directions to +1 for vectors pointing the same way, with 0 indicating orthogonality.^[6] In information retrieval, the cosine similarity of two documents ranges from 0 to 1, since term frequencies cannot be negative.^[6]

The Levenshtein distance between two strings is the minimum number of single-symbol insertions, deletions or substitutions needed to turn one into the other.^[3] The Soviet mathematician Vladimir Levenshtein introduced the Levenshtein distance in 1965, in the context of error-correcting codes.^[3] The Levenshtein distance between kitten and sitting is 3.^[3] DuckDB's damerau_levenshtein function extends Levenshtein distance by also allowing the transposition of adjacent characters as an edit.^[4]

The Hamming distance between two strings of equal length is the number of positions at which their symbols differ.^[9] The Hamming distance is named after the American mathematician Richard Hamming.^[9]

The Jaro–Winkler similarity is a variant of the Jaro distance of Matthew A. Jaro (1989), proposed in 1990 by William E. Winkler.^[7] The Jaro–Winkler similarity gives more favourable ratings to strings that match from the beginning, counting a common prefix of up to 4 characters.^[7] DuckDB's jaro_similarity and jaro_winkler_similarity functions return 0 instead of any similarity below an optional score_cutoff, which defaults to 0.^[4]

Compared with the alternatives

For two strings of the same length, the Hamming distance is an upper bound on the Levenshtein distance.^[3] The strings flaw and lawn have a Hamming distance of 4 but a Levenshtein distance of 2.^[3] Cosine similarity computed on vectors first centred by subtracting their means is equivalent to the Pearson correlation coefficient.^[6] A measure compares two values side by side in one row; finding which rows are closest to a given one is a search built on top of a measure, not the measure itself. (Meridian's view)

Sources

[1]Similarity measure - Wikipediasecondary source
[2]Edit distance - Wikipediasecondary source
[3]Levenshtein distance - Wikipediasecondary source
[4]Text Functions – DuckDBdocumentation
[5]Array Functions – DuckDBdocumentation
[6]Cosine similarity - Wikipediasecondary source
[7]Jaro–Winkler distance - Wikipediasecondary source
[8]Euclidean distance - Wikipediasecondary source
[9]Hamming distance - Wikipediasecondary source