Names are typed in many ways: Müller and Mueller, Mohammed and Muhammad, Martinez with two letters swapped. An equality test misses all of them. FUZZY_MATCH, one of the data quality operators of Oracle AI Database 26ai, returns how similar two strings are, by the algorithm you choose, so you can find likely duplicates and misspellings in SQL.
Code for This Guide
The main example is in the examples/character-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
fuzzy_match(algorithm, string1, string2
[, unscaled] [, relate_to_shorter] [, edit_tolerance n])| Algorithm | Measures |
|---|---|
| LEVENSHTEIN | Edits (insert, delete, replace) needed to turn one string into the other |
| DAMERAU_LEVENSHTEIN | The same, also counting a swap of two adjacent characters as one edit |
| JARO_WINKLER | Similarity weighted toward a common beginning; good for names |
| BIGRAM, TRIGRAM | Shared two- or three-character sequences |
| WHOLE_WORD_MATCH | Shared whole words |
| LONGEST_COMMON_SUBSTRING | The longest run of characters both strings contain |
The result is a score from 0 (nothing in common) to 100 (identical). With UNSCALED, it is the algorithm's raw value instead, such as the number of edits for LEVENSHTEIN.
Find a Name Despite Its Spelling
This query looks for customers whose last name is similar to Mueller, keeping those with a Jaro-Winkler score of 70 or more, and shows the scores of three algorithms and the raw edit count.
Example:
select c.first_name || ' ' || c.last_name as customer,
fuzzy_match(levenshtein, c.last_name, 'Mueller') as levenshtein,
fuzzy_match(jaro_winkler, c.last_name, 'Mueller') as jaro_winkler,
fuzzy_match(trigram, c.last_name, 'Mueller') as trigram,
fuzzy_match(levenshtein, c.last_name, 'Mueller', unscaled) as edits
from customers c
where fuzzy_match(jaro_winkler, c.last_name, 'Mueller') >= 70
order by jaro_winkler desc;Output:
CUSTOMER LEVENSHTEIN JARO_WINKLER TRIGRAM EDITS ______________ ______________ _______________ __________ ________ Neha Müller 72 80 20 2
Müller is two edits away from Mueller (ü to u, and an extra e), but Jaro-Winkler, which favors a shared beginning, rates the pair at 80, while the trigram score is low because few three-letter sequences are shared.
Compare the Algorithms
Different kinds of difference suit different algorithms. This query scores four pairs of names with five of them.
Example:
select a, b,
fuzzy_match(levenshtein, a, b) as levenshtein,
fuzzy_match(damerau_levenshtein, a, b) as damerau,
fuzzy_match(jaro_winkler, a, b) as jaro_winkler,
fuzzy_match(bigram, a, b) as bigram,
fuzzy_match(levenshtein, a, b, unscaled) as edits
from (values ('Smith', 'Smyth'), ('Mohammed', 'Muhammad'), ('Jon', 'John'),
('Martinez', 'Martniez')) t (a, b);Output:
A B LEVENSHTEIN DAMERAU JARO_WINKLER BIGRAM EDITS ___________ ___________ ______________ __________ _______________ _________ ________ Smith Smyth 80 80 89 50 1 Mohammed Muhammad 75 75 85 42 2 Jon John 75 75 93 33 1 Martinez Martniez 75 75 97 57 2
Martinez and Martniez differ by two swapped letters: LEVENSHTEIN counts two edits, while Jaro-Winkler, which tolerates transpositions near each other, scores 97. Short names such as Jon and John lose many points to a single edit under LEVENSHTEIN, since each edit is a large share of a short string. Bigram scores are the strictest here.
Things to Know
- Pick a threshold by testing on your own data; 70 to 85 with JARO_WINKLER is a common starting point for personal names.
- FUZZY_MATCH is computed for every pair you give it, so narrow the candidates first, for example by country or by the first letter.
- Combine it with PHONIC_ENCODE when names that sound alike should also match.
Related Guides
Conclusion
FUZZY_MATCH scores the similarity of two strings from 0 to 100 by an algorithm you choose: LEVENSHTEIN and DAMERAU_LEVENSHTEIN for edits, JARO_WINKLER for names, BIGRAM and TRIGRAM for shared sequences, and more. Use UNSCALED for the raw value, test thresholds on your data, and narrow the candidates before comparing.
