The REGEXP_SUBSTR function provides a superset of the functionality available with the SUBSTR function. While SUBSTR extracts a string from a specific location in the target, REGEXP_SUBSTR extracts a string that matches a given pattern, specified with a regular expression, from anywhere in the target. The following examples compare expressions using SUBSTR with those that use REGEXP_SUBSTR.
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"
SUBSTR vs REGEXP_SUBSTR
| Extracting a string from a fixed position, e.g. characters 2 and 3, is performed as follows. | |
select target , SUBSTR( target, 2, 2 ) substring from targets ; TARGET STRING SUBSTRING ---------- --------------- aaa aa abc bc bbb bb bcd cd cde de def ef efg fg |
select target , REGEXP_SUBSTR( target, '..', 2 ) substring from targets ; TARGET STRING SUBSTRING ---------- --------------- aaa aa abc bc bbb bb bcd cd cde de def ef efg fg |
| Matching a complex pattern is possible with REGEXP_SUBSTR, but not SUBSTR (unless you use other functions as well). The following example shows how to extract either the first "a", "c", or "f" character in the target. | |
-- -- not possible -- |
select target , REGEXP_SUBSTR( target, '^[acf]' ) substring from targets ; TARGET STRING SUBSTRING ---------- --------------- aaa a abc a bbb (null) bcd (null) cde c def (null) efg (null) |
To learn more about regular expression patterns in general see Pattern Matching.