Simple Oracle REGEXP_REPLACE function usage

Simple Oracle REGEXP_REPLACE function usage

REGEXP_REPLACE allows you to search for the function of the string's regular expression mode REPLACE function. By default,

This function returns source_char and replace_string to replace each instance in the regular expression mode.

The returned string is in the same character set source_char.

Syntax

The syntax of the REGEXP_REPLACE function in the Oracle database is:

REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )

Parameters source_char

Character Expression of the search value. This is usually a character column, which can be any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

pattern

Regular Expression

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 expression.
$	Matches the end of a string. If used with a match_parameter of ‘m’, it matches the end of a line anywhere within expression.
*	Matches zero or multiple.
+	Match one or more.
?	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	Match a numeric character.
\D	Match a non-numeric character.
\w	Match any word characters that contain underscores.
\W	Match any non-word characters.
\s	Matches any blank characters, including spaces, tabs, and page breaks.
\S	Match any non-blank characters.
\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.

replace_string

Optional. The matched mode replaces the replace_string string. If the replace_string parameter is omitted,

All matching modes will be deleted and the result string will be returned.

position

Optional. Search at the start of the string. If omitted, the default value is 1.

occurrence

Optional. Is a non-negative integer. The default value is 1, indicating the occurrence of the replacement operation:

If 0 is specified, all occurrences of the string will be replaced.

If a positive integer n is specified, the nth occurrence is replaced.

match_parameter

Optional. It allows you to modify the matching behavior of the REGEXP_REPLACE function. It can be a combination of the following:

Value	Description
‘c’	Case-sensitive matching.
‘i’	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, expression is assumed to be a single line.
‘x’	Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

EXAMPLE-match the first word

Let's begin to use the REGEXP_REPLACE function to replace the first word in the string.

For example:

SELECT REGEXP_REPLACE ('itmyhome is my network id', '^(\S*)', 'luck')FROM dual;Result: luck is my network id

In this example, "luck is my network id" is returned ",

Because it will find the first matched character at the beginning of the string, and then replace it with "luck"

EXAMPLE-match numeric characters

We will use the REGEXP_REPLACE function to match a single numeric character pattern.

For example:

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '\d', '#')FROM dual;Result: '#, #, and ## are numbers in this example'

In this example, the specified \ d number will be replaced with the # character

We can change our regular expression mode to search for only two digits.

For example:

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '(\d)(\d)', '#')FROM dual;Result: '2, 5, and # are numbers in this example'

This example replaces the (\ d) mode with two numbers. In this case, it skips 2 and 5 numeric values and replaces 10 with the # character.