RSS

UTL_MATCH – Oracle utility package to compare strings

22 Sep

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.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.

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.

 
Leave a comment

Posted by on 22 September, 2012 in Oracle, PL/SQL

 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: