Oracle UTL_MATCH Demos

Oracle UTL_MATCH Version 11.2.0.3

General Information

Purpose

The four functions included in the package use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source string into the destination string. This functionality is defined as a stand-alone package header compiled with PRAGMA INTERFACE(c....

AUTHID

DEFINER

Dependencies

None other than STANDARD

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

$ORACLE_HOME/rdbms/admin/utlmatch.sql

Subprograms

EDIT_DISTANCE	JARO_WINKLER
EDIT_DISTANCE_SIMILARITY	JARO_WINKLER_SIMILARITY

EDIT_DISTANCE

Returns the number of changes required to turn the source string into the destination string using the Levenshtein Distance algorithm

utl_match.edit_distance(s1 IN VARCHAR2, s2 IN VARCHAR2) RETURN PLS_INTEGER;

SELECT utl_match.edit_distance('expresso', 'espresso') DIST
FROM dual;

EDIT_DISTANCE_SIMILARITY

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match

utl_match.edit_distance_similarity(s1 IN VARCHAR2, s2 IN VARCHAR2) RETURN PLS_INTEGER;

SELECT utl_match.edit_distance_similarity('expresso', 'espresso') SIM
FROM dual;

JARO_WINKLER

Instead of simply calculating the number of steps required to change the source string to the destination string, determines how closely the two strings agree with each other and tries to take into account the possibility of a data entry error

utl_match.jaro_winkler(
s1 IN VARCHAR2,
s2 IN VARCHAR2)
RETURN BINARY_DOUBLE;

SELECT utl_match.jaro_winkler('expresso', 'espresso') DIST
FROM dual;

JARO_WINKLER_SIMILARITY

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match but tries to take into account possible data entry errors

utl_match.jaro_winkler_similarity(
s1 IN VARCHAR2,
s2 IN VARCHAR2)
RETURN PLS_INTEGER;

SELECT utl_match.jaro_winkler_similarity('expresso', 'expresso') SIM
FROM dual;