Name
regexp_substr — returns a single captured substring from matched substring
Synopsis
(varchar|nvarchar) regexp_substr ( |
in pattern (varchar|nvarchar) , |
| in str (varchar|nvarchar) , | |
in offset integer
); |
Parameters
pattern
the regexp pattern string
str
the data into which 1 (the first) or 0 matching substrings will be searched for
offset
the number of regexp subexpression who's match to return : 0 for the whole matching substring, 1 for the substring matching the first regexp subexpression and so on
Description
This function will return the whole string value of the first substring in "str" that matches the regexp in "pattern" or a sub part of the first match. The regexp syntax allows subexpressions to be marked in the regular expression (using the braces syntax). An example of such type of expression will be: '(2[34]).*(2[35])' which means a regular expression having two subexpressions: '2[34]' and '2[35]'.
Examples
Example24.253.Simple example
Let's apply the above regexp to the following source string: 22232225222323
SQL> select regexp_substr('(2[34]).*(2[35])','22242226222527', 0);
callret
VARCHAR
_______________________________________________________________________________
2422262225
1 Rows. -- 0 msec.
This returns the whole matched string from the expression.
SQL> select regexp_substr('(2[34]).*(2[35])','22242226222527', 1);
callret
VARCHAR
_______________________________________________________________________________
24
1 Rows. -- 0 msec.
This returns what has been matched for the first ('2[34]') regexp subexpression out of the whole matched substring (see above) : basically denoted as \1 in perl
SQL> select regexp_substr('(2[34]).*(2[35])','22242226222527', 2);
callret
VARCHAR
_______________________________________________________________________________
25
1 Rows. -- 10 msec.
This returns what has been matched for the second ('2[35]') regexp subexpression out of the whole matched substring. This is \2 in perl. Note that the text '22' (that corresponds to '.*' part of the regexp) is not returned by the above call because it is not contained in the second pair of braces.
![[Note]](images/note.png)