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. This example will return 'AndGrsGn' because it is searching for the first vowel (a, e, i, o, or u) in the string. The | pattern is used like an "OR" to specify more than one alternative.įor example: SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G') The next example that we will look at involves using the | pattern. In this example, we are going to replace all two-digit values from the address field in the contacts table with the value 'TBD'.Įxample - Match on more than one alternative Now, let's look how we would use the REGEXP_REPLACE function with a table column to replace two digit numbers.įor example: SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'TBD') In this case, it will skip over the 2 and 5 numeric values and replace 10 with a # character. This example will replace a number that has two digits side-by-side as specified by (\d)(\d). Result: '2, 5, and # are numbers in this example' We could change our pattern to search for only two-digit numbers.įor example: SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '(\d)(\d)', '#') It will replace the occurrences with a # character. This example will replace all numeric digits in the string as specified by \d. Result: '#, #, and # are numbers in this example' Let's look next at how we would use the REGEXP_REPLACE function to match on a single digit character pattern.įor example: SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '\d', '#') By default, whitespace characters are matched like any other character. By default, expression is assumed to be a single line. By default, the period is a wildcard.Įxpression 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.
It can be a combination of the following: ValueĪllows the period character (.) to match the newline character. It allows you to modify the matching behavior for the REGEXP_REPLACE function. If you specify 0 for this parameter, all appearances of pattern will be replaced in string. If omitted, it defaults to 1 which is the first appearance of pattern in string. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first position in the string. It is the position in string where the search will start. If the replacement_string parameter is omitted, the function simply removes all matched patterns, and returns the resulting string. Matched patterns will be replaced with replacement_string in string. Matches the preceding pattern at least n times, but not more than m times. Matches the preceding pattern at least n times. Matches the preceding pattern zero or one occurrence. Matches the preceding pattern one or more occurrences. Matches the preceding pattern zero or more occurrences. Matches the beginning of a string or matches at the end of a string before a newline character. Matches one collation element that can be more than one character. Matches the nth subexpression found within ( ) before encountering \n. Matches at least m times, but no more than n times. Used to group expressions as a subexpression. Used to specify a nonmatching list where you are trying to match any character except for the ones in the list. Used to specify a matching list where you are trying to match any one of the characters in the list. Used like an "OR" to specify more than one alternative. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression. It can be a combination of the following: Value The regular expression matching information.
It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The syntax for the REGEXP_REPLACE function in Oracle is: REGEXP_REPLACE( string, pattern ] ] ] ) Parameters or Arguments string The string to search.
0 kommentar(er)
