JARO_WINKLER_DISTANCE

Calculates and returns the Jaro-Winkler similarity, an edit distance between two sequences. It is useful for queries designed for short strings, such as finding similar names. It is a variant of the Jaro distance metric, to which it adds a prefix scale giving more favorable ratings for strings that match from the beginning. See also edit distance, which returns the Levenshtein distance between two strings.

Behavior type

Syntax

JARO_WINKLER_DISTANCE (string-expression1 , string-expression2 [ USING PARAMETERS prefix_scale=scale, prefix_length=length])

Arguments

string-expression1, string-expression2

The two VARCHAR expressions to compare. Neither can be NULL.

Parameters

scale

A FLOAT specifying the scale value by which to weight the importance of matching prefixes. Optional.

default = 0.1

length

An non-negative INT representing the maximum matching prefix length. Optional.

default = 4

Examples

Return only the names with a Jaro-Winkler distance from 'rode' that is greater than 0.6:

=> SELECT name FROM names WHERE JARO_WINKLER_DISTANCE('rode', name) > 0.6;
name
---------
fred
frieda
rodgers
rogers
(4 rows)

The Jaro-Winkler distance between 'help' and 'hello' given a prefix_scale of 0.1 and prefix_length of 0 is 0.783333333333333:

=> select JARO_WINKLER_DISTANCE('help', 'hello' USING PARAMETERS prefix_scale=0.1, prefix_length=0);
jaro_winkler_distance
-----------------------
0.783333333333333
(1 row)