SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we examine ways of specifying WHICH occurrence of a pattern we wish to match.

Other aspects of pattern matching will be kept constant throughout the tutorial, namely:

• "WHAT" will be a single character
• "HOW MANY" will be one repetition of the text
• "WHERE" will be from the first character in the target onward

REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR

By default a regular expression always matches the first occurrence of a pattern. To match the second, third, etc. occurrence of a pattern the REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR functions accept an "occurrence" parameter.

This example shows the default behaviour (matching the first occurrence) when no occurrence parameter is specified.

execute set_pattern( 'ab.' )

execute set_target( 'ab1-ab2'         )
execute add_target( 'ab1-ab2-ab3-ab4' )
execute add_target( 'def-def-def'     )
execute add_target( 'def-def-def-def' )

column target format a15

select
  'ab.' as pattern ,
  target ,
  regexp_substr( target, 'ab.' ) as matched_value ,
  regexp_instr( target, 'ab.' ) as position
from
  targets
;
 

PATTERN         TARGET          MATCHED_VALUE   POSITION
--------------- --------------- --------------- --------
ab.             ab1-ab2         ab1                    1
                ab1-ab2-ab3-ab4 ab1                    1
                def-def-def     (null)                 0
                def-def-def-def (null)                 0
 

This example shows how to match the third occurrence of a pattern. The same technique can be applied to any number of occurrences.

select
  'ab.' as pattern ,
  target ,
  regexp_substr( target, 'ab.', 1, 3 ) as matched_value ,
  regexp_instr( target, 'ab.', 1, 3 ) as position
from
  targets
;
 

PATTERN         TARGET          MATCHED_VALUE   POSITION
--------------- --------------- --------------- --------
ab.             ab1-ab2         (null)                 0
                ab1-ab2-ab3-ab4 ab3                    9
                def-def-def     (null)                 0
                def-def-def-def (null)                 0
 

REGEXP_LIKE

Unlike the three REGEXP functions we just examined, REGEXP_LIKE does not accept an occurrence parameter. The following examples show ways of working around this.

This example searches for targets with *exactly* three occurrences of 'def'

select target
from   targets
where  regexp_like( target, '(def.*){3}' )
and    not regexp_like( target, '(def.*){4}') ;
 

TARGET
---------------
def-def-def
 

This example searches for targets with *at least* three occurrences of 'def'

select target
from   targets
where  regexp_like( target, '(def.*){3}' ) ;
 

TARGET
---------------
def-def-def
def-def-def-def
 

This example searches for targets with at least three occurrences of 'ab.'

select target
from   targets
where  regexp_like( target, '(ab..*){3}' ) ;
 

TARGET
---------------
ab1-ab2-ab3-ab4
 

Of course, some of the requirements above could also be met without using REGEXP_LIKE by simply using a REGEXP function, as in the following examples.

Targets with at least three occurrences of 'def':

select target
from   targets
where  regexp_instr( target, 'def', 1, 3 ) != 0 ;
 

TARGET
---------------
def-def-def
def-def-def-def
 

Targets with at least three occurrences of 'ab.'

select target
from   targets
where  regexp_instr( target, 'ab.', 1, 3 ) != 0 ;
 

TARGET
---------------
ab1-ab2-ab3-ab4