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:
- 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
- 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
- UTL_MATCH.JARO_WINKLER returns BINARY_DOUBLE – Same as EDIT_DISTANCE but ignores data entry errors.
- UTL_MATCH.JARO_WINKLER_SIMILARITY returns PLS_INTEGER – Same as EDIT_DISTANCE_SIMILARITY but ignores data entry errors.
All the four functions take two string argument as inputs.
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:
Hope you find this informative and useful.