Oracle REGEXP_REPLACE function.

Preamble

The Oracle/PLSQL REGEXP_REPLACE function is an extension of the function REPLACE. This function, introduced in Oracle 10g, allows you to replace a sequence of characters in a string with a different set of characters using regular expression pattern mapping.

Syntax of the Oracle/PLSQL function REGEXP_REPLACE

REGEXP_REPLACE( string_id, pattern_id [, replacement_string_id [, start_position_id [, nth_appearance_id [, match_parameter_id ] ] ] ] ]

Parameters and function arguments

• string_id – A search line. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• pattern_id – Template. Regular expression for comparison. It can be a combination of the following values:

• replacement_string_id – It’s optional. The corresponding templates in the string will be replaced with replace_string. If the replacement_string parameter is omitted, the function simply deletes all matching templates and returns the resulting string.
• start_position_id – Optional. This is the position in the string from which the search will start. If this parameter is omitted, by default it is 1, which is the first position in the string.
• nth_appearance_id – Optional. This is the n-th view of the pattern in the string. If this option is omitted, it defaults to 1, which is the first entry of the template in the string. If you specify 0 for this parameter, all template entries in the string will be replaced.
• match_parameter_id – It’s optional. This allows you to change the compliance behavior for the REGEXP_REPLACE condition. This can be a combination of the following values:

The function REGEXP_REPLACE returns a string value.

If there are conflicting values for match_parameter, function REGEXP_REPLACE will use the last value.

The REGEXP_REPLACE function can be used in the following Oracle/PLSQL versions

Oracle 12c, Oracle 11g, Oracle 10g

Example of a match to the first word

Consider an example of using the REGEXP_REPLACE function to replace the first word in a string.

For example:

SELECT REGEXP_REPLACE ('Bing is a great search engine.', '^(\S*)', 'Google')
FROM dual;
--Result: 'Google is a great search engine.'

This example will return ‘Google is a great search engine.’ Because the beginning of the match at the beginning of the line, as indicated by ^, and then finds the first word according to (\S*). Then the function will replace this first word with ‘Google’.

Example of a number match

Let’s consider an example of how we will use the OracleREGEXP_REPLACE function to compare a pattern of digital characters.

For example:

SELECT REGEXP_REPLACE ('1, 4, and 10 numbers for example.', '\d', '@')
FROM dual;
--Result: @, @, and @@@ numbers for example.

This example will replace all numbers in the string, as specified in the \d template, with @.

We could change our template to search only for two-digit numbers

For example:

SELECT REGEXP_REPLACE ('1, 4, and 10 numbers for example', '(\d)(\d)', '@')
FROM dual;

--Result: 1, 4, and @ numbers for example

This example will replace a number that has two digits, as specified in the template (\d) (\d). In this case it will skip the numeric values 2 and 5 and replace 10 with @.

Now let’s see how we will use the REGEXP_REPLACE function with the table column to replace the two-digit numbers.

For example:

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

In this example, we will replace all two-digit values of the address field in the contacts table with ‘Str’.

This is an example of comparing several alternatives.
The next example we will look at involves using the | template. | the template is used as an ‘OR’ to specify multiple alternatives.

For instance:

SELECT REGEXP_REPLACE ('AeroSmith', 'a|e|i|o|u', 'R')
FROM dual;
-Result: ARrRSmRth

This example will return ‘ARrRSmRth’ because it looks for the first vowel (a, e, i, o or u) in the string. Since we didn’t specify a match_parameter value, the REGEXP_REPLACE function will perform a case sensitive search, which means that ‘A’ in ‘AeroSmith’ will not match.

We could modify our query to perform a case-insensitive search as follows:

SELECT REGEXP_REPLACE ('AeroSmith', 'a|e|i|o|u', 'R', 1, 0, 'i')
FROM dual;
--Result: RRrRSmRth

Now, since we specified match_parameter = ‘i’, the query will replace ‘A’ in the string. This time, ‘A’ in ‘AeroSmith’ will be matched with a template. Note also that we have specified the 5th parameter as 0 to replace all occurrences.

Now consider how you will use this function with the column.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​