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:
| Value |
Description |
| ^ |
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. |
| \n |
n 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. |
| \d |
Matches a digit character. |
| \D |
Matches a nondigit character. |
| \w |
Matches a word character. |
| \W |
Matches a nonword character. |
| \s |
Matches a whitespace character. |
| \S |
matches a non-whitespace character. |
| \A |
Matches the beginning of a string or matches at the end of a string before a newline character. |
| \Z |
Matches 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.
| Value |
Description |
| '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
Example 2
|
Home
Oracle
SQL
PL/SQL
MySQL
MongoDB
PostgreSQL
SQL Server
SQLite
Cassandra
DBMS
Python
Java
Aptitude
Interview Q
For Videos Join Our Youtube Channel: Join Now
