Oracle REGEXP_REPLACE() Function

REGEXP_REPLACE is a String function of Oracle. It is an extension of REPLACE function. This function is used for pattern matching and replacing the sequence of characters.

Syntax

Parameters

String : string for getting specified pattern.

Pattern: The regular expression pattern matching information. Some of them are as follows:

ValueDescription
^Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within an expression.
$Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within an expression.
*Matches zero or more occurrences.
+Matches one or more occurrences.
?Matches zero or one occurrence.
.Matches any character except NULL.
|Used like an "OR" to specify more than one alternative.
[ ]Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( )Used to group expressions as a subexpression.
{m}Matches m times.
{m,}Matches at least m times.
{m,n}Matches at least m times, but no more than n times.
\nn is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..]Matches one collation element that can be more than one character.
[::]Matches character classes.
[==]Matches equivalence classes.
\dMatches a digit character.
\DMatches a nondigit character.
\wMatches a word character.
\WMatches a nonword character.
\sMatches a whitespace character.
\Smatches a non-whitespace character.
\AMatches the beginning of a string or matches at the end of a string before a newline character.
\ZMatches at the end of a string.
*?Matches the preceding pattern zero or more occurrences.
+?Matches the preceding pattern one or more occurrences.
??Matches the preceding pattern zero or one occurrence.
{n}?Matches the preceding pattern n times.
{n,}?Matches the preceding pattern at least n times.
{n,m}?Matches the preceding pattern at least n times, but not more than m times.

start_position : it is optional. It is the starting position for matching the pattern from the given string.

nth_appearance : it is optional. It is the nth position up to which matching the pattern is to be done from the given string.

match_parameter : it is optional. It is used for matching the behavior for REGEXP_INSTR function. Some of them are as follows.

ValueDescription
'c'Perform case-sensitive matching.
'i'Perform case-insensitive matching.
'n'Allows the period character (.) to match the newline character. By default, the period is a wildcard.
'm'expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, the expression is assumed to be a single line.
'x'Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

Return

it returns replace string.

Example 1


Oracle String REGEXP_REPLACE() Function

Example 2


Oracle String REGEXP_REPLACE() Function
Next TopicOracle String




Latest Courses