RSS

REGEXP to simplify complex patterns of character sequences

02 Apr

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 2 April, 2012 in 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: