Oracle AI Database 26ai: A Comprehensive Guide to FUZZY_MATCH and Text Matching

1. Introduction

One of the most common challenges in database management is identifying duplicate or similar records when the same information is stored using different spelling variations.

Consider the following customer records:

ID	Customer Name
1	John Smith
2	Jon Smith
3	John Smyth
4	J. Smith
5	Michael Smith

A traditional SQL query using an equality condition returns only exact matches:

SELECT * FROM customers WHERE customer_name = 'John Smith';

This approach cannot identify spelling mistakes, missing characters, alternative spellings, or similar names.

Oracle AI Database 26ai introduces enhanced data quality capabilities that make text comparison and matching easier directly within SQL.

Two important technologies are particularly relevant:

• FUZZY_MATCH: Measures the similarity between two strings using different matching algorithms and returns a numerical similarity score.
• Oracle Text Fuzzy Matching: Expands search terms to include similarly spelled words through Oracle Text indexes.

These capabilities are particularly useful for duplicate detection, customer data cleansing, data migration, and advanced search applications.

2. Understanding FUZZY_MATCH

FUZZY_MATCH is a SQL operator available in Oracle AI Database 26ai for comparing strings and calculating their similarity.

Instead of requiring an exact character-by-character match, it evaluates how closely two strings resemble each other.

The similarity score is generally expressed on a scale of 0 to 100:

• 100: Exact match.
• 90: Very high similarity.
• 70: Moderate to high similarity.
• 50: Moderate similarity.
• 0: No meaningful similarity.

These values are illustrative. The interpretation depends on the selected algorithm and the characteristics of the input strings.

Example:

SELECT FUZZY_MATCH( LEVENSHTEIN, 'John Smith', 'Jon Smith' ) AS similarity_score FROM dual;

The function evaluates the character differences between the two strings and returns a similarity score.

Unlike byte-oriented comparison techniques, FUZZY_MATCH performs character-based comparisons, which is particularly relevant when working with multibyte character sets.

3. Supported FUZZY_MATCH Algorithms

Oracle 26ai provides several algorithms for different string-matching scenarios.

Algorithm	Description	Typical Use Case
LEVENSHTEIN	Measures character insertions, deletions, and substitutions.	Typographical errors
DAMERAU_LEVENSHTEIN	Also considers the transposition of adjacent characters.	Keyboard typing errors
JARO_WINKLER	Evaluates matching characters and common prefixes.	Personal and customer names
BIGRAM	Compares two-character sequences.	Short strings
TRIGRAM	Compares three-character sequences.	Product names and addresses
WHOLE_WORD_MATCH	Compares individual words.	Multi-word names
LONGEST_COMMON_SUBSTRING	Evaluates the longest common substring.	Similar text fragments

The choice of algorithm depends on the data and the matching requirements.

3.1 LEVENSHTEIN

The Levenshtein algorithm calculates the minimum number of editing operations required to transform one string into another.

It considers three operations:

1. Insertion: Adding a character.
2. Deletion: Removing a character.
3. Substitution: Replacing one character with another.

Example:

SELECT FUZZY_MATCH( LEVENSHTEIN, 'ORACLE', 'ORACEL' ) AS similarity FROM dual;

The last two characters have been transposed. Levenshtein may treat this as two separate character substitutions.

3.2 DAMERAU_LEVENSHTEIN

Damerau-Levenshtein extends the traditional Levenshtein algorithm by considering the transposition of adjacent characters as a single operation.

Example:

SELECT FUZZY_MATCH( LEVENSHTEIN, 'ORACLE', 'ORACEL' ) AS levenshtein_score, FUZZY_MATCH( DAMERAU_LEVENSHTEIN, 'ORACLE', 'ORACEL' ) AS damerau_score FROM dual;

This algorithm is particularly useful when users accidentally type adjacent characters in the wrong order.

3.3 JARO_WINKLER

Jaro-Winkler is especially useful for comparing short strings, personal names, and customer names.

It considers matching characters, their relative positions, and common prefixes.

Example:

SELECT FUZZY_MATCH( JARO_WINKLER, 'MICHAEL', 'MICHAEL' ) AS exact_match, FUZZY_MATCH( JARO_WINKLER, 'MICHAEL', 'MICHAELX' ) AS similar_match FROM dual;

This algorithm is a useful candidate for name-matching applications.

3.4 BIGRAM and TRIGRAM

BIGRAM divides a string into groups of two consecutive characters.

For example, the word ORACLE produces:

• OR
• RA
• AC
• CL
• LE

TRIGRAM divides the string into groups of three characters:

• ORA
• RAC
• ACL
• CLE

Example:

SELECT FUZZY_MATCH( BIGRAM, 'ORACLE DATABASE', 'ORACLE DATABSE' ) AS bigram_score, FUZZY_MATCH( TRIGRAM, 'ORACLE DATABASE', 'ORACLE DATABSE' ) AS trigram_score FROM dual;

These algorithms can be useful when comparing product catalogs, addresses, company names, and other text-based attributes.

3.5 WHOLE_WORD_MATCH

Unlike character-oriented algorithms, WHOLE_WORD_MATCH evaluates similarities at the word level.

Example:

SELECT FUZZY_MATCH( WHOLE_WORD_MATCH, 'Pawan Kumar Goel', 'Pavan Kumar G' ) AS similarity FROM dual;

Oracle documentation demonstrates a result of 67 for this example.

The algorithm also supports an optional EDIT_TOLERANCE parameter.

SELECT FUZZY_MATCH( WHOLE_WORD_MATCH, 'Pawan Kumar Goel', 'Pavan Kumar G', EDIT_TOLERANCE 60 ) AS similarity FROM dual;

EDIT_TOLERANCE specifies the permitted character-edit tolerance when determining whether words should be considered equivalent.

This parameter is supported specifically by WHOLE_WORD_MATCH.

3.6 LONGEST_COMMON_SUBSTRING

This algorithm evaluates the longest common sequence of characters shared by two strings.

Example:

SELECT FUZZY_MATCH( LONGEST_COMMON_SUBSTRING, 'ORACLE DATABASE', 'ORACLE CLOUD DATABASE' ) AS similarity FROM dual;

It can be useful when identifying common text fragments in longer strings.

4. Additional FUZZY_MATCH Parameters

Oracle 26ai provides additional parameters that influence how string similarity is calculated.

General syntax:

FUZZY_MATCH( algorithm, string1, string2 [, UNSCALED] [, RELATE_TO_SHORTER] [, EDIT_TOLERANCE number] )

4.1 UNSCALED

By default, FUZZY_MATCH returns a normalized similarity score.

Using UNSCALED returns the algorithm-specific raw measurement, such as an edit distance or a common-character measurement.

Example:

SELECT FUZZY_MATCH( LEVENSHTEIN, 'Mohamed Tarik', 'Mo Tariq' ) AS similarity_score, FUZZY_MATCH( LEVENSHTEIN, 'Mohamed Tarik', 'Mo Tariq', UNSCALED ) AS edit_distance FROM dual;

Oracle documentation demonstrates results of 54 and 6, respectively, for this example.

4.2 RELATE_TO_SHORTER

This parameter changes the reference length used during normalization.

• Default behavior: Uses the longer string.
• RELATE_TO_SHORTER: Uses the shorter string.

Example:

SELECT FUZZY_MATCH( DAMERAU_LEVENSHTEIN, 'Mohamed Tarik', 'Mo Tariq' ) AS default_score, FUZZY_MATCH( DAMERAU_LEVENSHTEIN, 'Mohamed Tarik', 'Mo Tariq', RELATE_TO_SHORTER ) AS shorter_score FROM dual;

This parameter can be useful when comparing strings with significantly different lengths.

4.3 EDIT_TOLERANCE

This parameter is available specifically for WHOLE_WORD_MATCH.

It controls the permitted character-edit tolerance when comparing individual words.

5. PHONIC_ENCODE: Phonetic Matching

Another important operator available in Oracle AI Database 26ai is PHONIC_ENCODE.

While FUZZY_MATCH evaluates textual similarity, PHONIC_ENCODE converts words into phonetic codes.

This makes it possible to identify words that sound similar but are spelled differently.

Oracle uses the Double Metaphone algorithm for phonetic encoding.

Two encoding options are available:

• DOUBLE_METAPHONE: Returns the primary phonetic code.
• DOUBLE_METAPHONE_ALT: Returns the alternative phonetic code when available; otherwise, it returns the primary code.

Example:

SELECT PHONIC_ENCODE( DOUBLE_METAPHONE, 'Smith' ) AS primary_code, PHONIC_ENCODE( DOUBLE_METAPHONE_ALT, 'Smith' ) AS alternative_code FROM dual;

Example output:

PRIMARY_CODE	ALTERNATIVE_CODE
SM0	XMT

Another example:

SELECT PHONIC_ENCODE( DOUBLE_METAPHONE, 'Schmidt' ) AS code1, PHONIC_ENCODE( DOUBLE_METAPHONE_ALT, 'Schmidt' ) AS code2 FROM dual;

Example output:

CODE1	CODE2
XMT	SMT

The function also supports an optional max_code_len parameter, which accepts an integer between 1 and 12.

SELECT PHONIC_ENCODE( DOUBLE_METAPHONE_ALT, 'poignant', 10 ) AS phonetic_code FROM dual;

Phonetic matching is particularly useful for names and geographical locations. However, Double Metaphone is not equally accurate for every language. Special attention should be given to non-English names and language-specific pronunciation rules.

6. Oracle Text Matching

Oracle Text is another important technology for advanced text searching in Oracle AI Database 26ai.

Unlike FUZZY_MATCH, which compares two strings directly, Oracle Text uses specialized text indexes to search large collections of documents and text data.

Typical use cases include:

• Searching large document repositories.
• Finding words with spelling variations.
• Searching for similarly spelled terms.
• Word stemming.
• Searching multiple terms and phrases.
• Ranking search results by relevance.

It is important to understand that Oracle Text fuzzy searching is not a new feature introduced exclusively in 26ai. It has been available in previous Oracle releases and continues to provide advanced text-search functionality.

6.1 Creating an Oracle Text Index

First, create a sample table:

CREATE TABLE documents ( document_id NUMBER, document_name VARCHAR2(200), document_text CLOB );

Insert sample records:

INSERT INTO documents VALUES ( 1, 'Oracle Database', 'Oracle Database administration and performance tuning' ); 

INSERT INTO documents VALUES ( 2, 'Oracle Architecture', 'Oracle database architecture and internal components' ); 

INSERT INTO documents VALUES ( 3, 'PostgreSQL Guide', 'PostgreSQL database administration guide' ); 

INSERT INTO documents VALUES ( 4, 'Database Performance', 'Database performance optimization and SQL tuning' ); 

COMMIT;

Now create an Oracle Text CONTEXT index:

CREATE INDEX documents_text_idx ON documents(document_text) INDEXTYPE IS CTXSYS.CONTEXT;

This creates a specialized text index on the DOCUMENT_TEXT column.

You can now use the CONTAINS operator to search document contents.

SELECT document_id, document_name, SCORE(1) AS relevance_score FROM documents WHERE CONTAINS( document_text, 'Oracle', 1 ) > 0 ORDER BY SCORE(1) DESC;

The query uses:

• CONTAINS: Performs the text search.
• document_text: The indexed text column.
• Oracle: The search term.
• SCORE(1): Returns the relevance score.
• > 0: Returns matching documents.

6.2 Oracle Text Fuzzy Matching

Oracle Text provides a fuzzy search operator that expands a search term to include similarly spelled words.

General syntax:

FUZZY( term, score, numresults, weight )

Parameter descriptions:

Parameter	Description
term	Search term
score	Minimum similarity score (1-80)
numresults	Maximum number of expanded terms (1-5000)
weight	Determines whether similarity scores contribute to result ranking

Example:

SELECT document_id, document_name, SCORE(1) AS relevance_score FROM documents WHERE CONTAINS( document_text, 'FUZZY(ORACLE, 70, 10, WEIGHT)', 1 ) > 0 ORDER BY SCORE(1) DESC;

Oracle Text performs the following operations:

1. Uses ORACLE as the search term.
2. Searches the text index for similarly spelled terms.
3. Applies the specified similarity threshold.
4. Expands the search using up to 10 alternative terms.
5. Uses WEIGHT to incorporate similarity scores into relevance ranking.

The NOWEIGHT option can be used when similarity scores should not influence term weighting.

Oracle Text fuzzy searches require a search term containing at least three characters. Supported languages include English, French, German, Italian, Spanish, and Japanese, among others. Turkish language support should not be assumed without checking the relevant Oracle Text language configuration and documentation.

6.3 Oracle Text Stemming

Stemming is another useful Oracle Text capability.

It allows searches to match different grammatical forms of a word by considering their common root.

For example:

• Speak
• Speaks
• Spoke
• Spoken

These words can be associated with the same linguistic root.

Create a BASIC_WORDLIST preference:

BEGIN 

 CTX_DDL.CREATE_PREFERENCE( 'STEM_FUZZY_PREF', 'BASIC_WORDLIST' );
 CTX_DDL.SET_ATTRIBUTE( 'STEM_FUZZY_PREF', 'FUZZY_MATCH', 'ENGLISH' );
 CTX_DDL.SET_ATTRIBUTE( 'STEM_FUZZY_PREF', 'FUZZY_SCORE', '1' );
 CTX_DDL.SET_ATTRIBUTE( 'STEM_FUZZY_PREF', 'FUZZY_NUMRESULTS', '5000' );
 CTX_DDL.SET_ATTRIBUTE( 'STEM_FUZZY_PREF', 'SUBSTRING_INDEX', 'TRUE' );
 CTX_DDL.SET_ATTRIBUTE( 'STEM_FUZZY_PREF', 'STEMMER', 'ENGLISH' ); 

END; 
/

 

Recreate the index using the new preference:

CREATE INDEX documents_text_idx ON documents(document_text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ( 'WORDLIST STEM_FUZZY_PREF' );

This configuration enables the specified fuzzy matching and stemming options in the Oracle Text wordlist configuration.

DBA Note: FUZZY_MATCH, FUZZY_SCORE, FUZZY_NUMRESULTS, and STEMMER are Oracle Text wordlist attributes. They should not be confused with the parameters of the SQL FUZZY_MATCH operator.

7. Practical Example: Detecting Duplicate Customer Records

Let’s consider a real-world database scenario.

A customer table contains duplicate records because customers have been registered using different spelling variations.

7.1 Create a Sample Table

CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(200), city VARCHAR2(100) );

Insert sample data:

INSERT INTO customers VALUES (1, 'John Smith', 'London'); 
INSERT INTO customers VALUES (2, 'Jon Smith', 'London'); 
INSERT INTO customers VALUES (3, 'John Smyth', 'London'); 
INSERT INTO customers VALUES (4, 'Michael Smith', 'New York'); 
INSERT INTO customers VALUES (5, 'John Smith', 'Manchester'); 
INSERT INTO customers VALUES (6, 'John Smit', 'London'); 
INSERT INTO customers VALUES (7, 'David Brown', 'London'); 
COMMIT;

7.2 Find Similar Customer Names

The following query identifies records similar to John Smith:

SELECT customer_id, customer_name, city, FUZZY_MATCH( JARO_WINKLER, customer_name, 'John Smith' ) AS similarity_score FROM customers WHERE FUZZY_MATCH( JARO_WINKLER, customer_name, 'John Smith' ) >= 70 ORDER BY similarity_score DESC;

The threshold of 70 is used as a minimum similarity requirement.

This approach can be implemented in customer search screens, registration validation, and data cleansing applications.

7.3 Compare Multiple Algorithms

It is often useful to evaluate the same records using different algorithms.

SELECT customer_id, customer_name, FUZZY_MATCH( LEVENSHTEIN, customer_name, 'John Smith' ) AS levenshtein_score, FUZZY_MATCH( DAMERAU_LEVENSHTEIN, customer_name, 'John Smith' ) AS damerau_score, FUZZY_MATCH( JARO_WINKLER, customer_name, 'John Smith' ) AS jaro_winkler_score, FUZZY_MATCH( TRIGRAM, customer_name, 'John Smith' ) AS trigram_score FROM customers ORDER BY jaro_winkler_score DESC;

This makes it possible to compare how different algorithms evaluate the same customer records.

7.4 Detect Duplicate Records Across a Table

Another important use case is comparing records against each other rather than against a predefined search term.

SELECT c1.customer_id AS customer_id_1, c1.customer_name AS customer_name_1, c2.customer_id AS customer_id_2, c2.customer_name AS customer_name_2, FUZZY_MATCH( JARO_WINKLER, c1.customer_name, c2.customer_name ) AS similarity_score FROM customers c1 JOIN customers c2 ON c1.customer_id < c2.customer_id AND c1.city = c2.city WHERE FUZZY_MATCH( JARO_WINKLER, c1.customer_name, c2.customer_name ) >= 80 ORDER BY similarity_score DESC;

The condition c1.customer_id < c2.customer_id prevents the same pair of records from being evaluated twice.

This technique is particularly useful for identifying potential duplicate records.

For large tables, however, self-joins can become expensive. In production environments, candidate records should first be reduced using selective attributes such as city, country, customer type, or other relevant columns.

8. Performance Considerations for Oracle DBAs

FUZZY_MATCH and Oracle Text have different execution models and should be evaluated separately from a performance perspective.

8.1 FUZZY_MATCH Performance

Consider the following query:

SELECT * FROM customers WHERE FUZZY_MATCH( LEVENSHTEIN, customer_name, 'John Smith' ) >= 80;

On large tables, this operation can be expensive because the function may need to evaluate many candidate rows.

Recommended practices include:

• Reduce the candidate dataset using selective filters.
• Normalize input strings where appropriate.
• Use candidate grouping strategies for large datasets.
• Avoid unnecessary evaluation of multiple algorithms.
• Review execution plans and SQL runtime statistics.

For example:

SELECT * FROM customers WHERE city = 'London' AND FUZZY_MATCH( JARO_WINKLER, customer_name, 'John Smith' ) >= 80;

An appropriate index on the CITY column may help reduce the number of candidate records.

However, an ordinary index should not be assumed to provide direct index access for the FUZZY_MATCH expression itself.

8.2 Oracle Text Performance

Oracle Text uses specialized indexes and can provide efficient searching across large document collections.

DBAs should consider:

• CONTEXT index creation and maintenance costs.
• Index synchronization requirements.
• Lexer and wordlist configurations.
• The number of expanded terms in fuzzy searches.
• Index storage and disk consumption.

A high FUZZY_NUMRESULTS setting may increase the number of alternative terms considered during a search.

Therefore, search accuracy and resource consumption should be balanced.

9. FUZZY_MATCH vs. Oracle Text

Feature	FUZZY_MATCH	Oracle Text
Primary purpose	Compare two strings	Search indexed text
Execution model	SQL function	Text index
Similarity score	0-100	Oracle Text SCORE
Typographical error tolerance	Yes	Yes
Levenshtein	Yes	Not as the same SQL algorithm
Jaro-Winkler	Yes	Not as the same SQL algorithm
Stemming	No	Yes
Document search	Not its primary purpose	Yes
Phonetic matching	With PHONIC_ENCODE	Separate text-search mechanisms

The two technologies complement each other rather than replace one another.

Use FUZZY_MATCH when comparing individual values or identifying potential duplicate records.

Use Oracle Text when searching large collections of indexed documents and text content.

Conclusion

Oracle AI Database 26ai provides valuable SQL-based data quality capabilities through FUZZY_MATCH and PHONIC_ENCODE.

These operators make it possible to perform advanced string comparison and phonetic matching directly inside the database.

Key use cases include:

1. Duplicate customer detection.
2. Person and company name matching.
3. Identifying spelling mistakes in addresses and product descriptions.
4. Record matching during database migrations.
5. Integrating data from multiple systems.
6. Data cleansing and data quality projects.
7. Improving search applications that handle incorrectly entered words.

For Oracle DBAs, one of the most important benefits is the ability to implement many string-matching operations directly in SQL without necessarily relying on external applications or specialized data quality tools.

However, a similarity score does not prove that two records belong to the same person or organization. In production systems, matching decisions should also consider other attributes, such as date of birth, customer number, address, and additional identifying information.

In summary: Oracle AI Database 26ai enhances SQL-based data matching with FUZZY_MATCH and PHONIC_ENCODE, while Oracle Text continues to provide powerful indexed text-search capabilities, including fuzzy matching and stemming.