Tuesday, June 23, 2009

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i

I really love using the REGEXP_REPLACE function and REGEXP_LIKE operator in Oracle 10G - and have been spoiled by their ease of use as a result.

One of my clients, however - is sort of "stuck" on Oracle 9iR2 due to regression testing cost requirements of an upgrade. The problem is that we still have to do development on the 9iR2 database - and I REALLY want my regular expressions!

So - I figured that I would take a stab at writing my own REGEXP_REPLACE and REGEXP_LIKE functions.

Here's the source code - in case you want the same functionality in Oracle 9iR2:


create or replace
FUNCTION regexp_replace (
source_char IN VARCHAR2
, pattern IN VARCHAR2
, replace_string IN VARCHAR2
, POSITION IN PLS_INTEGER DEFAULT 1
, occurrence IN PLS_INTEGER DEFAULT 0
, match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 AS
/*************************************************************************
* Program : regexp_replace
* Version : 1.0
* Author : Philip Moore
* Date : 20-JUN-2009 Anno Domini
* Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2
* Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
*************************************************************************/
-- Variables
l_source_string VARCHAR2 (32767);
l_temp_string VARCHAR2 (32767);
l_flags VARCHAR2 (10);
l_occurrence PLS_INTEGER;
l_end_of_pattern_pos PLS_INTEGER;
l_string_pos PLS_INTEGER;
BEGIN
-- Substr the source_char to start at the position specified
l_source_string := SUBSTR (source_char, POSITION);

-- Set up the flags argument
IF occurrence = 0 THEN
l_flags := 'g';
ELSE
l_flags := NULL;
END IF;

l_flags := l_flags || match_parameter;

-- Now replace the regular expression pattern globally if "g"
IF INSTR (l_flags, 'g') > 0 THEN
owa_pattern.CHANGE (line => l_source_string
, from_str => pattern
, to_str => replace_string
, flags => l_flags);
-- Not a global replace - loop until the "occurrence"th occurrence is replaced...
ELSE
l_string_pos := 0;
l_occurrence := 0;
WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP
l_string_pos := l_string_pos + 1;

l_end_of_pattern_pos := owa_pattern.amatch (line => l_source_string
, from_loc => l_string_pos
, pat => pattern
, flags => match_parameter);

IF l_end_of_pattern_pos != 0 THEN
l_occurrence := l_occurrence + 1;
END IF;
END LOOP;

IF l_occurrence = occurrence THEN
l_temp_string := SUBSTR (l_source_string
, l_string_pos
, (l_end_of_pattern_pos - l_string_pos));
owa_pattern.CHANGE (line => l_temp_string
, from_str => pattern
, to_str => replace_string
, flags => l_flags);
l_source_string := SUBSTR (l_source_string
, 1
, l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);
END IF;
END IF;

-- Piece the string back together if needed...
IF POSITION > 1 THEN
l_source_string := SUBSTR (source_char
, 1
, (POSITION - 1)) || l_source_string;
END IF;

RETURN l_source_string;
END regexp_replace;
/


CREATE OR REPLACE FUNCTION regexp_like (source_char IN VARCHAR2
, pattern IN VARCHAR2
, match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
/*************************************************************************
* Program : regexp_like
* Version : 1.0
* Author : Philip Moore
* Date : 20-JUN-2009 Anno Domini
* Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2
* Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
*************************************************************************/
-- Variables
l_return INTEGER;
BEGIN
IF owa_pattern.match (line => source_char
, pat => pattern
, flags => match_parameter) THEN
l_return := 1;
ELSE
l_return := 0;
END IF;

RETURN l_return;
END regexp_like;
/




The Functions in Action:
Here's a demo of my new Oracle 9iR2 REGEXP_REPLACE function:

SELECT regexp_replace ('5138675309', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS phone 
FROM v$version
WHERE banner = 'Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production';

Result:
PHONE
----------------
(513) 867-5309




Some Watchouts:
The "OWA_PATTERN" package isn't as powerful as the built-in REGEXP% functions that Oracle 10g Provides - for example - it doesn't support the "" OR operator. It is also much slower than those natively built-in 10g functions - so don't use these to find the last digit in pi :)

I hope these are helpful in your "stuck in Oracle 9i" endeavors!

Special thanks to Greg Houston's http://formatmysourcecode.blogspot.com/ for helping me format the code for blogger!

11 comments:

  1. Thanks!! this saved my day. Great work!

    ReplyDelete
  2. Another saved day here! Many thanks!

    ReplyDelete
  3. Thanks! I suppose that finally I would do the same, but my first reaction when I found more than 200 calls to REGEXP_LIKE in my code to be migrated from 11 to 9 till yesterday, was panic. Your article was an unexpected gift from uncle G.

    ReplyDelete
  4. Thanks a lot. I used your code for my need :).
    I have written code for REGEXP_INSTR

    http://oracleappstech-vinoth.blogspot.com/2012/03/regexpinstr-function-for-oracle-9i.html

    ReplyDelete
  5. REGEXP_SUBSTR ANYBODY HAVE A DLL FUNCTION

    ReplyDelete
  6. thanks a thousand times man ! you saved my day

    ReplyDelete