UTL_MATCH – Oracle utility package to compare strings

Today I came across an oracle utility package to compare strings viz. UTL_MATCH. UTL_MATCH comes with Oracle version 11g release 1.

The four functions in this package are:

1. UTL_MATCH.EDIT_DISTANCE returns PLS_INTEGER  – Use to see the number of characters which needs to be changed in first string to get second string
2. UTL_MATCH.EDIT_DISTANCE_SIMILARITY returns PLS_INTEGER – In scale of 0 to 100 rate the match. 0 indicates no match and 100 indicates perfect match
3. UTL_MATCH.JARO_WINKLER returns BINARY_DOUBLE – Same as EDIT_DISTANCE but ignores data entry errors.
4. UTL_MATCH.EDIT_DISTANCE_SIMILARITY returns PLS_INTEGER – Same as EDIT_DISTANCE_SIMILARITY but ignores data entry errors.

All the four functions take two string argument as inputs.

Some examples:

select UTL_MATCH.EDIT_DISTANCE(‘PLSQL’,'SQL’) from dual; — This returns 2. Indicating that changing two characters in first string should get second string

select UTL_MATCH.EDIT_DISTANCE(‘HANG SENG’,'NASDAQ’) from dual;  This returns 7. Indicating that changing 7 characters in first string should get second string

select UTL_MATCH.edit_distance_similarity(‘PLSQL’,'SQL’) from dual; — This Returns 60.

select UTL_MATCH.edit_distance_similarity(‘HANG SENG’,'NASDAQ’) from dual; — Returns 23.

For more info you can checkout following reference links:

1. http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/u_match.htm

2. http://psoug.org/reference/utl_match.html

Hope you find this informative and useful.