RSS

Category Archives: PL/SQL

Oracle PL/SQL Developers

UTL_MATCH – Oracle utility package to compare strings

Last updated on November 26th, 2014 at 07:07 am

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 September 22, 2012 in Oracle, PL/SQL

 

Tags: , , , ,

Weekly Participation Award for 31 August 2012 (PL/SQL Challenge)

Last updated on November 26th, 2014 at 07:07 am

Its my pleasure to share with you all that I am awarded “Weekly Participation Award for 31 August 2012” by PL/SQL Challenge.
The award is for the week ending 31 August 2012. The prize is O’Reilly Media Oracle eBook of my choice.

Visit my public profile on PL/SQL Challenge for other achievements

Cheers!

 
Leave a comment

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

 

Tags: ,

REGEXP to simplify complex patterns of character sequences

Last updated on November 26th, 2014 at 07:07 am

The PL/SQL language implements most of the built-in SQL functions as native PL/SQL functions. It then adds some of its own. Either way, you should be as familiar as possible with these built-ins so that you can avoid writing programs to perform calculations that are already done for you (and likely to run much faster, since the built-ins are implemented in C).

Regular expressions specify patterns to search for in string data using standardized syntax conventions. A regular expression can specify complex patterns of character sequences.

With Oracle 10g release, REGEXP functions were provided which simplified complex search to great extent.

Regular expression like REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REPLACE.

REGEXP_LIKE:
This function searches a character column for a pattern. Use this function in the WHERE clause of a query to return rows matching the regular expression you specify.
See the Oracle Database SQL Reference for syntax details on the REGEXP_LIKE function.

REGEXP_REPLACE:
This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern you specify.
See the Oracle Database SQL Reference for syntax details on the REGEXP_REPLACE function.

REGEXP_INSTR:
This function searches a string for a given occurrence of a regular expression pattern. You specify which occurrence you want to find and the start position to search from. This function returns an integer indicating the position in the string where the match is found.
See the Oracle Database SQL Reference for syntax details on the REGEXP_INSTR function.

REGEXP_SUBSTR:
This function returns the actual substring matching the regular expression pattern you specify.
See the Oracle Database SQL Reference for syntax details on the REGEXP_SUBSTR function.

Following is an example of “Predicate Pushing in Views“. I have tried to show the usage of REGEXP_SUBSTR:

DROP VIEW PLCH_FILTER_ERRORS_V
/
DROP TABLE PLCH_TEST_TBL
/
DROP TABLE PLCH_ERROR_TBL
/
CREATE TABLE PLCH_TEST_TBL (TEST_col VARCHAR2 (9))
/
CREATE TABLE PLCH_ERROR_TBL (ERROR_COL VARCHAR2 (9))
/
BEGIN
 INSERT INTO PLCH_TEST_TBL (TEST_COL)
 VALUES ('123456789');
INSERT INTO PLCH_TEST_TBL (TEST_COL)
 VALUES ('x23456789');
INSERT INTO PLCH_ERROR_TBL (ERROR_COL)
 VALUES ('x23456789');

 COMMIT;
END;
/
CREATE OR REPLACE VIEW PLCH_FILTER_ERRORS_V
AS
 SELECT TEST_COL FROM PLCH_TEST_TBL
 MINUS
 SELECT ERROR_COL FROM PLCH_ERROR_TBL
/
--/*Returns 1*/
SELECT TO_NUMBER (SUBSTR (TEST_COL, 1, 1))
 FROM PLCH_FILTER_ERRORS_V
 WHERE REGEXP_LIKE (TEST_COL, '[0-9]', 'i');
--/*Returns INVALID NUMBER*/
SELECT *
 FROM (SELECT TO_NUMBER (SUBSTR (TEST_COL, 1, 1)) SUB1
 FROM PLCH_FILTER_ERRORS_V)
 WHERE SUB1 > 0;
--/*Returns INVALID NUMBER*/
SELECT sub1
 FROM (SELECT TO_NUMBER (SUBSTR (TEST_COL, 1, 1)) SUB1
 FROM PLCH_FILTER_ERRORS_V
 WHERE REGEXP_LIKE (test_col, '[0-9]', 'i'))
 WHERE SUB1 > 0;
--/*Returns 1*/ 
SELECT *
 FROM (SELECT TO_NUMBER (REGEXP_SUBSTR (TEST_COL, 1, 1)) SUB1
 FROM PLCH_FILTER_ERRORS_V)
 WHERE SUB1 > 0;

In Oracle 11g, REGEXP_COUNT is the newly introduced regular expression function which returns the number of rows for which the patter is found.

 
Leave a comment

Posted by on April 2, 2012 in PL/SQL

 

Tags: , , , , , ,

High Accuracy in Week (18 February 2012) in PL/SQL Challenge

Last updated on November 26th, 2014 at 07:07 am

I am delighted to share with you all that I am awarded “High Accuracy in Week” by PL/SQL Challenge.
The award is for the week 18th February 2012. The prize I received is Amazon.com US$25 Gift Card 🙂

Visit my public profile on PL/SQL Challenge for other achievements

Cheers!

 
Leave a comment

Posted by on February 20, 2012 in Oracle, PL/SQL

 

Tags: , , ,

 
snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake