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