Regular Expressions

REGEXP_SUBSTR

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11747.html]SQL Snippets: Regular Expressions - REGEXP_SUBSTR[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11747.html">SQL Snippets: Regular Expressions - REGEXP_SUBSTR</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Regular Expressions - REGEXP_SUBSTR
  • URL (href): http://www.sqlsnippets.com/en/topic-11747.html