Table of Contents
This chapter briefly introduces the UTL_MATCH package, and describes how to use the functions of the package.
UTL_MATCH is used to calculate the degree of similarity between two strings.
It provides functions that use the Levenshtein Algorithm to calculate the Edit Distance and the Jaro-Winkler Algorithm to calculate the Match Score.
This section describes the functions provided by the UTL_MATCH package, in alphabetical order.
Calculates the Edit Distance between two string using the Levenshtein Algorithm.
Details about the EDIT_DISTANCE function are as follows:
Prototype
UTL_MATCH.EDIT_DISTANCE
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN PLS_INTEGER;
Parameters
| Parameter | Description |
|---|---|
| str1 | Operand 1. |
| str2 | Operand 2. |
Return Value
| Return Value | Description |
|---|---|
| PLS_INTEGER | Edit Distance between str1and str2. Returns -1 if either or both of str1or str2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_MATCH.EDIT_DISTANCE('apple','aaple'));
END;
/
1
Calculates the Edit Distance between two strings using the Levenshtein Algorithm and normalizes the result (percent).
Details about the EDIT_DISTANCE_SIMILARITY are as follows:
Prototype
UTL_MATCH.EDIT_DISTANCE_SIMILARITY
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN PLS_INTEGER;
Parameters
| Parameter | Description |
|---|---|
| str1 | Operand 1. |
| str2 | Operand 2. |
Return Value
| Return Value | Description |
|---|---|
| PLS_INTEGER | Normalized value (to the string with the longer length) of the Edit Distance between str1and str2 in percent. Returns 100 if both strings are NULL. Returns 0 if either one of str1or str2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_MATCH.EDIT_DISTANCE_SIMILARITY('apple', 'aaple'));
END;
/
81
Calculates the Match Score between two string using the Jaro-Winkler Algorithm.
Details about the JARO_WINKLER function are as follows:
Prototype
UTL_MATCH.JARO_WINKLER
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN BINARY_DOUBLE;
Parameters
| Parameter | Description |
|---|---|
| str1 | Operand 1. |
| str2 | Operand 2. |
Return Value
| Return Value | Description |
|---|---|
| BINARY_DOUBLE | Match Score between str1and str2. Returns 1 if either or both of str1or str2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_MATCH.JARO_WINKLER('apple','aaple'));
END;
/
.88
Calculates the Match Score (in percent) between two string using the Jaro-Winkler Algorithm.
Details about the JARO_WINKLER_SIMILARITY function are as follows:
Prototype
UTL_MATCH.JARO_WINKLER_SIMILARITY
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN PLS_INTEGER;
Parameters
| Parameter | Description |
|---|---|
| str1 | Operand 1. |
| str2 | Operand 2. |
Return Value
| Return Value | Description |
|---|---|
| PLS_INTEGER | Match Score (in percent) between str1and str2. Returns 1 if either or both of str1or str2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_MATCH.JARO_DISTANCE_SIMILARITY('apple','aaple'));
END;
/
88