With the old INSTR function, it was relatively easy to find the last occurrence of a particular string by passing in a -1 for the position to start from
CREATE TABLE foo(
str varchar2(100)
);
SQL> insert into foo values( 'abcabcabc' );
1 row created.
1* select instr( str, 'a', -1 ) from foo
SQL> /
INSTR(STR,'A',-1)
-----------------
7
That doesn't appear to be possible using REGEXP_INSTR
1* select regexp_instr( str, 'a', -1 ) from foo
SQL> /
select regexp_instr( str, 'a', -1 ) from foo
*
ERROR at line 1:
ORA-01428: argument '-1' is out of range
And I don't know of a generic way to take a regular expression than finds the Nth occurrence of an expression and change it to find the last occurrence short of doing a REVERSE on the string and then subtracting that from the length of the string. Obviously, we could create an expression that would find the last occurrence of a pattern, but that involves building and testing two different patterns, one for the Nth occurrence and one for the last occurrence which seems counterproductive.
Am I missing something?
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Justin