11g: New Regular Expression Functions

RegExp_Count has been added in 11g. It's pretty straight forward. In this example, I'm looking through this popular tongue-twister for the occurrences of the letters 'pic', and not surprisingly it will tell me '2.'

SELECT REGEXP_COUNT 
    ( 'Peter Piper picked a peck of pickled peppers' -- Searched string
    , 'pic'                                          -- Pattern to find
    , 1                                              -- Start position
    , 'i')                                           -- 'i' = case insensitive
           As Count_of_Pic
FROM dual;

The other new options are RegExp_Instr, and RegExp_Substr, which allows you to look for some part of a string. I haven't come up with a good example of when I'll need this, but I'm sure life will present an opportunity.

The substring numbers are based upon the left parens.

(-----------1--------------)
 (-----2----)               
  (-3--) (4)   (----5-----) 
123      4     5             -- Left Parens Order
(((Lynn) (T)), (Programmer))

For example, the 1st substring goes from the 1st left paren over to its matching right one. The 2nd substring goes from the next left paren over to its matching one, etc.

My example only shows the RegExp_Subst, but the RegExp_Instr is almost the same except for the one highlighted line that's looking for whether the Instr number returned is the actual place, or the one to the right of it.

SELECT
REGEXP_INSTR ('Lynn T, Programmer'           -- Searched string
             ,'(((Lynn) (T)), (Programmer))' -- Pattern to find
             , 1                             -- Start pos
             , 1                             -- Occurrence
             , 0                             -- Return option (0=this,1=next position)             , 'i'                           -- Case insensitive
             , '&Subpattern'                 -- Subpattern #
             )
FROM dual;

When the following code is run 5 times, each subpattern is requested and is displayed.

SELECT
REGEXP_SUBSTR ('Lynn T, Programmer'          -- Searched string
             ,'(((Lynn) (T)), (Programmer))' -- Pattern to find
             , 1                             -- Start pos
             , 1                             -- Occurrence
             , 'i'                           -- Case insensitive
             , '&Subpattern'                 -- Subpattern #
             )
FROM dual;