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!
Very Good, I do it and works.
ReplyDeletetks
Thanks!! this saved my day. Great work!
ReplyDeleteAnother saved day here! Many thanks!
ReplyDeletethis is epic! thanks! :D
ReplyDeleteThanks! 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.
ReplyDeleteThanks a lot. I used your code for my need :).
ReplyDeleteI have written code for REGEXP_INSTR
http://oracleappstech-vinoth.blogspot.com/2012/03/regexpinstr-function-for-oracle-9i.html
REGEXP_SUBSTR ANYBODY HAVE A DLL FUNCTION
ReplyDeletethanks a thousand times man ! you saved my day
ReplyDeleteAwesome!!..Thanks a ton
ReplyDelete