REGEXP_REPLACE Function
This Oracle tutorial explains how to use the Oracle/PLSQL REGEXP_REPLACE function with syntax and examples.
Description
The Oracle/PLSQL REGEXP_REPLACE function is an extension of the REPLACE function. This function, introduced in Oracle 10g, will allow you to replace a sequence of characters in a string with another set of characters using regular 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 regular expression matching information. It can be a combination of the following:
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 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. - replacement_string
- Optional. Matched patterns will be replaced with replacement_string in string. If the replacement_string parameter is omitted, the function simply removes all matched patterns, and returns the resulting string.
- start_position
- Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.
- nth_appearance
- Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string. If you specify 0 for this parameter, all appearances of pattern will be replaced in string.
- match_parameter
- Optional. It allows you to modify the matching behavior 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 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.
Note
- If there are conflicting values provided for match_parameter, the REGEXP_REPLACE function will use the last value.
Applies To
The REGEXP_REPLACE function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g
Example - Match on First Word
Let's start by using the REGEXP_REPLACE function to replace the first word in a string.
For example:
SELECT REGEXP_REPLACE ('TechOnTheNet is a great resource', '^(\S*)', 'CheckYourMath')
FROM dual;
Result: 'CheckYourMath is a great resource'
This example will return 'CheckYourMath is a great resource' because it will start the match at the beginning of the string as specified by
^ and then find the first word as specified by (\S*). The function will then replace this first word with 'CheckYourMath'.Example - Match on Digit Characters
Let's look next at how we would use the REGEXP_REPLACE function to match on a single digit 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'
This example will replace all numeric digits in the string as specified by
\d. It will replace the occurrences with a # character.
We could change our pattern 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 number that has two digits side-by-side as specified by
(\d)(\d). In this case, it will skip over the 2 and 5 numeric values and replace 10 with a # character.
Now, let's look how we would use the REGEXP_REPLACE function with a table column to replace two digit numbers.
For example:
SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'TBD') FROM contacts;
In this example, we are going to replace all two-digit values from the address field in the contacts table with the value 'TBD'.
Example - Match on more than one alternative
The next example that we will look at involves using the
| pattern. The | pattern is used like an "OR" to specify more than one alternative.
For example:
SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G')
FROM dual;
Result: 'AndGrsGn'
This example will return 'AndGrsGn' because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_REPLACE function will perform a case-sensitive search which means that the 'A' in 'Anderson' will not be matched.
We could modify our query as follows to perform a case-insensitive search as follows:
SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G', 1, 0, 'i')
FROM dual;
Result: 'GndGrsGn'
Now because we have provide a match_parameter of 'i', the query will replace 'A' in the string. This time, the 'A' in 'Anderson' will be found as a match. Notice also that we specified 0 as the 5th parameter so that all occurrences would be replaced.
Now, let's quickly show how you would use this function 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 results that would be returned by 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 involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to replace 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 example will replace the second character ('e') in 'TechOnTheNet' because it is replacing the first occurrence of a vowel (a, e, i, o, or u) in the string.
Second Occurrence
Next, we will extract for the second occurrence 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 replacing the second occurrence 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 replace the ninth character ('e') in 'TechOnTheNet' because it is replacing the third occurrence of a vowel (a, e, i, o, or u) in the string.
No comments:
Post a Comment