Application Express 3.1 finally landed. If you want to know it's new features, take a look at New features. Oracle XE users got their own section under Oracle Database XE and Application Express 3.1 which looks similiar to the 3.0.1 instructions. apex.oracle.com has been upgraded already, so if anyone just wants to test drive the new version, go there and get yourself a workspace.

After explaining the somewhat strange behaviour of regular expression in OTN-Thread regular expression problem I encountered another problem today, where this behvaiour can actually used to solve a requirement.

Take a look at REGEXP_REPLACE: How to replace a list of words to null. To illustrate what is going on, I changed the SELECT to show the findings of that regular expression:

WITH t AS
     (SELECT 'the quick brown fox jumps command of and king to if all' AS str     
        FROM dual
       UNION 
      SELECT 'it it'
        FROM dual)
SELECT t.str
     , REGEXP_REPLACE (t.str, '((^| )(and|the|if|but|for|to|is|how|or|i|a|it|of)[^ ]*)', ':\1:') AS mod_str
  FROM t
;

STR                                      MOD_STR
---------------------------------------- ----------------------------------------
it it                                    :it:: it:
the quick brown fox jumps command of and :the: quick brown fox jumps command: of:
 king to if all                          : and: king: to:: if:: all:

WITH x AS(SELECT 'air the quick brown fox jumps command of and king to if all'  AS str
            FROM DUAL
           UNION ALL
          SELECT 'it it'
            FROM DUAL
           UNION ALL
          SELECT 'and xyz the if but for to is how or i a it of'
            FROM DUAL
           UNION ALL
          SELECT 'and the if but for to is how or i a it of'
            FROM DUAL
           UNION ALL
          SELECT 'xyz and the if but for to is how or i a it of'
            FROM DUAL
           UNION ALL
          SELECT 'and the if but for to is how or i a it of xyz'
            FROM DUAL
           UNION ALL
          SELECT 'yet the if but for another to is how or i a it of test'
            FROM DUAL)
SELECT str  
     , mod_str
  FROM x
 MODEL
  PARTITION BY (ROWNUM rn)
   DIMENSION BY (0 dim)
   MEASURES(str str, str mod_str)
   RULES ITERATE(99) UNTIL (mod_str[0]= PREVIOUS(mod_str[0]))
                (mod_str[0] = REGEXP_REPLACE(mod_str[0], '(^| )(and|the|if|but|for|to|is|how|or|i|a|it|of)( |$)', '\1\3'))
  ;

STR                                      MOD_STR
---------------------------------------- ----------------------------------------
air the quick brown fox jumps command of air  quick brown fox jumps command   kin
 and king to if all                      g   all

yet the if but for another to is how or  yet     another         test
i a it of test

and xyz the if but for to is how or i a   xyz
it of

and the if but for to is how or i a it o
f

it it
xyz and the if but for to is how or i a  xyz
it of

and the if but for to is how or i a it o              xyz
f xyz