UTL_MATCH Package
1. Choose a comparison
Connect in Oracle compatibility mode with a role that can execute the package. The functions take two strings.
| Function | Result |
|---|---|
|
Minimum number of insertions, deletions and substitutions. |
|
Edit-distance score from 0 to 100. |
|
Similarity score from 0 to 1, with extra weight for a shared prefix. |
|
Jaro–Winkler score on an integer scale from 0 to 100. |
A larger similarity score means a closer match. Use edit distance when the number of changes matters. When comparing names, Jaro–Winkler can help rank likely spelling variations. The comparison is case-sensitive, so normalize both inputs explicitly when case should be ignored.
2. Compare example strings
SELECT utl_match.edit_distance('kitten', 'sitting') AS edits,
utl_match.edit_distance_similarity('cat', 'cat') AS exact_score;
SELECT utl_match.jaro_winkler('MARTHA', 'MARHTA') > 0.9 AS close_match,
utl_match.jaro_winkler_similarity('cat', 'cat') AS exact_score;
The first query returns 3 edits and an exact-match score of 100. The second returns true for the similar names and 100 for the identical strings.