Oracle String/Char Function - Oracle/PLSQL REGEXP_SUBSTR Function

REGEXP_SUBSTR returns string of data type VARCHAR2 or CLOB. REGEXP_SUBSTR uses regular expressions to specify the start and end of the returned string.


The simplest format for this function is:

REGEXP_SUBSTR(source_string, pattern_to_find)

The general format for the function with all the options is:

               pattern-to-find [, 
  • source_string:the source string
  • pattern-to-find:the pattern to search for.
  • position:where to start.
  • occurrence:occurrence of the pattern-to-find in the source-string you want to search for.
  • match-parameter:for further customizing.
    • "i" in match-parameter can be used for caseinsensitive matching
    • "c" in match-parameter can be used for casesensitive matching
    • "n" in match-parameter allows the period to match the new line character
    • "m" in match-parameter allows for more than one line in source-string


The metacharacters list:

\Specify the escape sequence
\dDigit character
\DNon-digit character
\wWord character
\WNon-word character
\sWhitespace character
\SNon-whitespace character
\AMatches only at the beginning of a string or before a newline character at the end of a string
\ZMatches only at the end of a string
^Matches the position at the start of the string.
$Matches the position at the end of the string.
*Matches the preceding character zero or more times.
+Matches the preceding character one or more times.
?Matches the preceding character zero or one time.
*?Matches the preceding pattern element 0 or more times
+?Matches the preceding pattern element 1 or more times
??Matches the preceding pattern element 0 or 1 time
{n}Matches a character exactly n times, where n is an integer.
{n,}Matches the preceding pattern element at least n times
{n,m}Matches a character at least n times and at most m times, where n and m are both integers.
.Matches any single character except null.
(pattern)A subexpression that matches the specified pattern.
x|yMatches x or y, where x and y are one or more characters. war|peace matches war or peace.
[abc]Matches any of the enclosed characters.
[a-z]Matches any character in the specified range.
[:alphanum:]matches alphanumeric characters 0-9, A-Z, and a-z.
[:alpha:]matches alphabetic characters A-Z and a-z.
[:blank:]matches space or tab.
[:digit:]matches digits 0-9.
[:graph:]matches non-blank characters.
[:lower:]matches lowercase alphabetic characters a-z.
[:print:]is similar to [:graph:] except [:print:] includes the space character.
[:punct:]matches punctuation characters .,"`, and so on.
[:space:]matches all whitespace characters.
[:upper:]matches all uppercase alphabetic characters A-Z.
[:xdigit:]matches characters permissible in a hexadecimal number 0-9, A-F, and a-f.
[..]Matches one collation element, like a multicharacter element.
[==]Specifies equivalence classes.
\nA backreference to an earlier capture, where n is a positive integer.



\nmatches the newline character
\\matches \
\(matches (
^Amatches if A is the first character in the string.
$Bmatches if B is the last character in the string.
f*dmatches flood, food, and so on.
fo+dmatches fod, food, and so on.
fo?dmatches fd and fod only.
fo{2}dmatches food.
fo{2,3}dmatches food and foood only.
[ab]bcmatches abc and bbc.
[a-c]bcmatches abc, bbc, and cbc.

Example 2

SQL> SELECT REGEXP_SUBSTR('Mississippi', 'si', 1, 2, 'i') FROM dual;
-- w  ww  .j  a v a2 s  .com

SQL> SELECT REGEXP_SUBSTR('asdf asdf lord ','l[[:alpha:]]{2}') AS result FROM dual;