Using UTL_MATCH for Fuzzy Search in Oracle APEX

Introduction

Oracle APEX applications often require efficient search functionality. However, traditional SQL LIKE searches do not handle typos or similar words effectively.

A fuzzy search can improve search accuracy by finding similar words based on string matching techniques. Oracle provides the UTL_MATCH package, which allows comparison between words using:
EDIT_DISTANCE – Measures the number of changes required to convert one word into another.
EDIT_DISTANCE_SIMILARITY – Returns similarity as a percentage (0-100).
JARO_WINKLER_SIMILARITY – Compares strings based on common prefixes and transpositions.

This post will demonstrate how to implement a fuzzy search using UTL_MATCH in Oracle APEX.

Why Use Fuzzy Search?

Handles typos (e.g., “Jon” vs. “John”).
Finds similar names or products (e.g., “Alexandra” vs. “Aleksandra”).
Improves user experience by ranking search results based on similarity.

Step 1: Create a Sample Table

We will create a sample customer table for testing fuzzy search queries.

CREATE TABLE customers (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name  VARCHAR2(50),
    last_name   VARCHAR2(50),
    full_name   VARCHAR2(100) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
);

Using a virtual column ( full_name ) ensures consistent searching without storing redundant data.

Insert some sample records:

INSERT INTO customers (first_name, last_name) VALUES ('John', 'Doe');
INSERT INTO customers (first_name, last_name) VALUES ('Jon', 'Doh');
INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Smith');
INSERT INTO customers (first_name, last_name) VALUES ('Janet', 'Smyth');
COMMIT;

Step 2: Fuzzy Search Query Using UTL_MATCH

We will use UTL_MATCH.EDIT_DISTANCE_SIMILARITY to rank similar names.

SELECT id, first_name, last_name,
       UTL_MATCH.EDIT_DISTANCE_SIMILARITY(full_name, 'Jon Doe') AS similarity_score
FROM customers
ORDER BY similarity_score DESC;

This query compares the full_name column against “Jon Doe” and returns a similarity score.

Example Output:

ID	First Name	Last Name	Similarity Score
1	John	Doe	95
2	Jon	Doh	90
3	Jane	Smith	40
4	Janet	Smyth

Step 3: Creating a Fuzzy Search Function in PL/SQL

To use fuzzy search in APEX Interactive Reports or Dynamic Actions, create a function:

CREATE OR REPLACE FUNCTION fuzzy_search(p_search VARCHAR2)
RETURN SYS_REFCURSOR
AS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
    SELECT id, first_name, last_name,
           UTL_MATCH.EDIT_DISTANCE_SIMILARITY(full_name, p_search) AS similarity_score
    FROM customers
    WHERE UTL_MATCH.EDIT_DISTANCE_SIMILARITY(full_name, p_search) > 50  -- Filter weak matches
    ORDER BY similarity_score DESC;

    RETURN v_cursor;
END fuzzy_search;

This function returns customers with at least 50% similarity to the search term.

Step 4: Using Fuzzy Search in an APEX Report

Create a Search Page in APEX

Add a Text Field (P1_SEARCH_NAME) for user input.
Add an Interactive Report on the CUSTOMERS table.

Modify Report Query

Set the SQL Query of the report to:

SELECT id, first_name, last_name,
       UTL_MATCH.EDIT_DISTANCE_SIMILARITY(full_name, :P1_SEARCH_NAME) AS similarity_score
FROM customers
WHERE UTL_MATCH.EDIT_DISTANCE_SIMILARITY(full_name, :P1_SEARCH_NAME) > 50
ORDER BY similarity_score DESC;

The report dynamically filters and ranks similar names.

Step 5: Using a PL/SQL Dynamic Action for Real-Time Search

Instead of reloading the page, use a Dynamic Action for real-time search.

Create a Dynamic Action

Event: Key Release
Selection Type: Item
Item: P1_SEARCH_NAME
Action: Execute PL/SQL Code
Code:

DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    v_cursor := fuzzy_search(:P1_SEARCH_NAME);
    APEX_UTIL.OPEN_CACHED_QUERY(v_cursor);
END;

Affected Elements: Interactive Report (Refresh after execution).

This enables instant fuzzy search as the user types.

Step 6: Enhancing Search with JARO_WINKLER_SIMILARITY

The Jaro-Winkler Similarity algorithm is useful for short names or misspellings.

Modify the query:

SELECT id, first_name, last_name,
       UTL_MATCH.JARO_WINKLER_SIMILARITY(full_name, :P1_SEARCH_NAME) AS similarity_score
FROM customers
WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(full_name, :P1_SEARCH_NAME) > 80
ORDER BY similarity_score DESC;

Jaro-Winkler gives higher weight to common prefixes, making it ideal for name searches.

Conclusion

UTL_MATCH provides a powerful way to implement fuzzy search in Oracle APEX.

Key Benefits:
✔ Handles misspellings and typos.
✔ Ranks results based on similarity score.
✔ Works with Interactive Reports and Dynamic Actions for instant search.
✔ Supports multiple algorithms (EDIT_DISTANCE, JARO_WINKLER).

chinnappa