How to Compare Strings with UTL_MATCH (edit distance, Jaro-Winkler)

People misspell names. A search for exact matches finds nothing when a caller says Al Mansori and the customer is Al Mansoori. UTL_MATCH measures how similar two strings are, with the edit distance and the Jaro-Winkler algorithm, so queries can find close matches instead of only exact ones.

Code for This Guide

The main examples are in the examples/pkg-raw-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

utl_match.edit_distance(s1, s2)                 -- number of edits
utl_match.edit_distance_similarity(s1, s2)      -- 0 to 100
utl_match.jaro_winkler(s1, s2)                  -- 0 to 1
utl_match.jaro_winkler_similarity(s1, s2)       -- 0 to 100

The edit distance counts the single-character insertions, deletions, and substitutions that turn one string into the other. Jaro-Winkler scores matching characters and transpositions, and favors strings that start the same.

Find a Misspelled Name

Example:

-- a caller spells the name "Al Mansori": which customers could it be?
select first_name, last_name,
       utl_match.edit_distance(last_name, 'Al Mansori')            as edits,
       utl_match.edit_distance_similarity(last_name, 'Al Mansori') as edit_pct,
       utl_match.jaro_winkler_similarity(last_name, 'Al Mansori')  as jw_pct
from   customers
where  utl_match.jaro_winkler_similarity(last_name, 'Al Mansori') >= 80
order  by jw_pct desc, first_name;

select utl_match.edit_distance('Mumbai', 'Bombay')                 as edits,
       utl_match.jaro_winkler('MARTHA', 'MARHTA')                  as jaro_winkler
from   dual;

Output:

FIRST_NAME    LAST_NAME         EDITS    EDIT_PCT    JW_PCT
_____________ ______________ ________ ___________ _________
Sakura        Al Mansoori           1          91        98
Zoë           Al Mansoori           1          91        98

   EDITS JARO_WINKLER
________ _____________________
       3 0.9611111111111111

Al Mansoori is one edit from Al Mansori, 91 percent by edit distance and 98 by Jaro-Winkler, so both customers with that name are found. Mumbai and Bombay are three edits apart, and the classic MARTHA and MARHTA score 0.96 because Jaro-Winkler treats the swapped letters as a small difference.

Search Airports by a Typed City

Example:

-- a typed city name with mistakes: find the closest airports
select airport_code, city,
       utl_match.jaro_winkler_similarity(upper(city), upper('Singapur'))  as score
from   airports
order  by score desc
fetch  first 3 rows only;

Output:

AIRPORT_CODE    CITY            SCORE
_______________ ____________ ________
SIN             Singapore          93
BLR             Bengaluru          72
IST             Istanbul           61

Singapur, the German spelling, matches Singapore with 93. Uppercasing both sides makes the comparison case-insensitive, since UTL_MATCH compares characters exactly.

Things to Know

• A threshold of about 80 to 90 for Jaro-Winkler similarity works well for names; test it with your data.
• The functions run for every row compared, so narrow the rows first on a large table, for example by first letter or country.
• For phonetic matching, SOUNDEX compares how English words sound.

Related Guides

• How to Escape Text with UTL_I18N
• Oracle SOUNDEX Function: A Simple Guide

Conclusion

UTL_MATCH scores how close two strings are, by edit distance or Jaro-Winkler, as raw values or percentages. Use the similarity functions with a threshold to find names and places despite typing mistakes and spelling variants.