The REGEXP_INSTR function is similar to the INSTR function except that it finds the location of a pattern of characters, specified with a regular expression, instead of finding the location of a simple string literal. The following examples compare searches using INSTR with those that use REGEXP_INSTR.
Before proceeding however, the following code will set up some test data for us (see Setup if you want to create the SET_TARGET and ADD_TARGET procedures on your own test system).
execute set_target( 'aaa' ) execute add_target( 'abc' ) execute add_target( 'bbb' ) execute add_target( 'bcd' ) execute add_target( 'cde' ) execute add_target( 'def' ) execute add_target( 'efg' ) column target heading "TARGET|STRING"
INSTR vs REGEXP_INSTR
| Matching a string literal is performed as follows. | |
select target , INSTR( target, 'b' ) position from targets ; TARGET STRING POSITION ---------- -------- aaa 0 abc 2 bbb 1 bcd 1 cde 0 def 0 efg 0 |
select target , REGEXP_INSTR( target, 'b' ) position from targets ; TARGET STRING POSITION ---------- -------- aaa 0 abc 2 bbb 1 bcd 1 cde 0 def 0 efg 0 |
| Matching a complex pattern is possible with REGEXP_INSTR, but not INSTR (unless you use other text manipulation functions as well). The following example shows how to find the position of either the first "a", "c", or "f" character in the target. | |
-- -- not possible -- |
select target , REGEXP_INSTR( target, '[acf]' ) position from targets ; TARGET STRING POSITION ---------- -------- aaa 1 abc 1 bbb 0 bcd 2 cde 1 def 3 efg 2 |
To learn more about regular expression patterns in general see Pattern Matching.