Oracle / PLSQL: REGEXP_REPLACE Function

This article is written about how to use the Oracle/PLSQL REGEXP_REPLACE feature with syntax and examples.

Description

The Oracle/PLSQL REGEXP_REPLACE function is an extension of the REPLACE function. This function, delivered in Oracle 10g, will permit you to exchange a sequence of characters in a string with any other set of characters the usage of ordinary expression pattern matching.

Syntax

The syntax for the REGEXP_REPLACE function in Oracle is:

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Parameters or Arguments

string The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. pattern The everyday expression matching information. It can be a aggregate of the following: Value Description ^ Matches the commencing of a string. If used with a match_parameter of ‘m’, it suits the begin of a line anywhere inside expression. $ Matches the stop of a string. If used with a match_parameter of ‘m’, it fits the stop of a line anywhere within expression. * Matches zero or extra occurrences. + Matches one or greater occurrences. ? Matches zero or one occurrence. . Matches any persona except NULL. | Used like an “OR” to specify extra than one alternative. [ ] Used to specify a matching listing the place you are trying to healthy any one of the characters in the list. [^ ] Used to specify a nonmatching listing where you are trying to in shape any personality 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 variety between 1 and 9. Matches the nth subexpression discovered inside ( ) before encountering \n. [..] Matches one collation aspect that can be more than one character. [::] Matches personality classes. [==] Matches equivalence classes. \d Matches a digit character. \D Matches a nondigit character. \w Matches a phrase character. \W Matches a nonword character. \s Matches a whitespace character. \S matches a non-whitespace character. \A Matches the commencing of a string or suits at the quit of a string before a newline character. \Z Matches at the give up of a string. *? Matches the previous pattern zero or more occurrences. +? Matches the previous sample one or greater occurrences. ?? Matches the preceding pattern zero or one occurrence. {n}? Matches the preceding pattern n times. {n,}? Matches the previous sample at least n times. {n,m}? Matches the previous pattern at least n times, but now not extra than m times. replacement_string Optional. Matched patterns will be replaced with replacement_string in string. If the replacement_string parameter is omitted, the characteristic certainly removes all matched patterns, and returns the ensuing string. start_position Optional. It is the function in string where the search will start. If omitted, it defaults to 1 which is the first function in the string. nth_appearance Optional. It is the nth appearance of sample in string. If omitted, it defaults to 1 which is the first appearance of sample in string. If you specify zero for this parameter, all appearances of pattern will be changed in string. match_parameter Optional. It lets in you to regulate the matching conduct for the REGEXP_REPLACE function. It can be a combination of the following: Value Description ‘c’ Perform case-sensitive matching. ‘i’ Perform case-insensitive matching. ‘n’ Allows the duration persona (.) to match the newline character. By default, the period is a wildcard. ‘m’ expression is assumed to have multiple lines, the place ^ is the begin of a line and $ is the stop of a line, regardless of the position of these 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.

Returns

The REGEXP_REPLACE characteristic returns a string value.

Note

If there are conflicting values supplied for match_parameter, the REGEXP_REPLACE characteristic will use the last value. See additionally the REPLACE function.

Applies To

The REGEXP_REPLACE feature can be used in the following variations of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

Example – Match on First Word

Let’s start by the usage of the REGEXP_REPLACE function to change the first phrase in a string.

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet is a great resource', '^(\S*)', 'CheckYourMath')
FROM dual;

Result: 'CheckYourMath is a great resource'

This instance will return ‘CheckYourMath is a magnificent resource’ because it will start the in shape at the starting of the string as certain via ^ and then find the first phrase as specified by way of (\S*). The feature will then exchange this first phrase with ‘CheckYourMath’.

Example – Match on Digit Characters

Let’s seem subsequent at how we would use the REGEXP_REPLACE characteristic to healthy on a single digit personality 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'

This instance will replace all numeric digits in the string as distinct by \d. It will replace the occurrences with a # character.

We ought to alternate our sample to search for only two-digit numbers.

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 will replace a wide variety that has two digits side-by-side as distinctive via (\d)(\d). In this case, it will pass by over the two and 5 numeric values and change 10 with a # character.

Now, let’s seem to be how we would use the REGEXP_REPLACE function with a table column to substitute two digit numbers.

For example:

SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'TBD')
FROM contacts;

In this example, we are going to substitute all two-digit values from the address area in the contacts table with the fee ‘TBD’.

Example – Match on more than one alternative

The subsequent instance that we will seem to be at includes the usage of the | pattern. The | pattern is used like an “OR” to specify greater than one alternative.

For example:

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G')
FROM dual;

Result: 'AndGrsGn'

This example will return ‘AndGrsGn’ due to the fact it is looking out for the first vowel (a, e, i, o, or u) in the string. Since we did now not specify a match_parameter value, the REGEXP_REPLACE function will function a case-sensitive search which skill that the ‘A’ in ‘Anderson’ will no longer be matched.

We should adjust our query as follows to operate a case-insensitive search as follows:

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G', 1, 0, 'i')
FROM dual;

Result: 'GndGrsGn'

Now due to the fact we have provide a match_parameter of ‘i’, the question will replace ‘A’ in the string. This time, the ‘A’ in ‘Anderson’ will be found as a match. Notice also that we specified zero as the 5th parameter so that all occurrences would be replaced.

Now, let’s shortly exhibit how you would use this characteristic with a column.

So let’s say we have a contact table with the following data:

contact_id last_name 1000 Anderson 2000 Smith 3000 Johnson

Now, let’s run the following query:

SELECT contact_id, last_name, REGEXP_REPLACE (last_name, 'a|e|i|o|u', 'G', 1, 0, 'i') AS "New Name"
FROM contacts;

These are the outcomes that would be returned by way of the query:

contact_id last_name New Name 1000 Anderson GndGrsGn 2000 Smith SmGth 3000 Johnson JGhnsGn

Example – Match on nth_occurrence

The next example that we will look at includes the nth_occurrence parameter. The nth_occurrence parameter approves you to pick out which incidence of the pattern you wish to change in the string.

First Occurrence

Let’s look at how to replace the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 1, 'i')
FROM dual;

Result: 'TZchOnTheNet'

This instance will substitute the 2nd character (‘e’) in ‘TechOnTheNet’ because it is changing the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will extract for the 2nd prevalence of a pattern in a string.

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 2, 'i')
FROM dual;

Result: 'TechZnTheNet'

This example will replace the fifth character (‘O’) in ‘TechOnTheNet’ because it is changing the 2nd incidence of a vowel (a, e, i, o, or u) in the string.

Third Occurrence

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 3, 'i')
FROM dual;

Result: 'TechOnThZNet'

This example will exchange the ninth personality (‘e’) in ‘TechOnTheNet’ due to the fact it is changing the 0.33 occurrence of a vowel (a, e, i, o, or u) in the string.