Oracle REGEXP_REPLACE

The Oracle REGEXP_REPLACE() function replaces a sequence of characters that matches a regular expression pattern with another string.

The REGEXP_REPLACE() function is an advanced version of the REPLACE() function.

Syntax

The following illustrates the syntax of the Oracle REGEXP_REPLACE() function:

REGEXP_REPLACE ( source_string, search_pattern [, replacement_string [, star_position [, nth_occurrence [, match_parameter ] ] ] ] )
Code language: SQL (Structured Query Language) (sql)

Arguments

The REGEXP_REPLACE() function takes 6 arguments:

1) source_string

is the string to be searched for.

2) search_pattern

is the regular expression pattern for which is used to search in the source string.

3) replacement_string

is the string that replaces the matched pattern in the source string. This argument is optional and its default value is null.

4) start_position

is an integer that determines the position in the source string where the search starts. The start_position is also optional. If not mentioned, the start_position is 1, which is the beginning position of the source string.

5) nth_occurrence

is a non-positive integer that indicates which position the replacement should take place. If nth_position is 0, the REGEXP_REPLACE() function will replace all occurrences of the match. Otherwise, the REGEXP_REPLACE() function will replace the nth occurrence.

6) match_parameter

is a literal string that changes the default matching behavior of the function. The behavior of the match_parameter in this function is the same for in the REGEXP_SUBSTR() function. Refer to REGEXP_SUBSTR() function for detailed information.

Return Value

The REGEXP_REPLACE() function returns a string with matched pattern replaced by another string.

Examples

A) Removing special characters from a string

Sometimes, your database may contain special characters. The following statement uses the REGEXP_REPLACE() function to remove special characters from a string:

SELECT REGEXP_REPLACE('Th♥is∞ is a dem☻o of REGEXP_♫REPLACE function','[^a-z_A-Z ]') FROM dual;
Code language: SQL (Structured Query Language) (sql)

The following is the result:

This is a demo of REGEXP_REPLACE function
Code language: SQL (Structured Query Language) (sql)

This query is useful in a data cleaning task process.

The following picture illustrates the meaning of the regular expression [^a-z_A-Z ]

Oracle REGEXP_REPLACE regular expression 1

B) Masking sensitive information

The following statement hides the middle part of a credit card for security purposes. You can apply this technique in E-commerce, Banking, and other Financial applications that require strict security.

SELECT regexp_replace( '4024007187788590', '(^\d{3})(.*)(\d{4}$)', '\1**********\3' ) credit_card FROM dual;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

402**********8590
Code language: SQL (Structured Query Language) (sql)

This picture explains the meaning of the regular expression: (^\d{3})(.*)(\d{4}$)

C) Removing redundant spaces

The following statement removes redundant spaces, the space character that appears more than one, in a string:

SELECT regexp_replace( 'This line contains more than one spacing between words' , '( ){2,}', ' ' ) regexp_replace FROM dual;
Code language: SQL (Structured Query Language) (sql)

Output:

This line contains more than one spacing between words
Code language: SQL (Structured Query Language) (sql)

Here is the meaning of the regular expression ( ){2,}

Oracle REGEXP_REPLACE regular expression 3

D) Reformat phone numbers

See the following employees table in the sample database:

employees table

The following statement searches for phone numbers that match the pattern xxx.xxx.xxxx. and reformats them using the pattern (xxx) xxx-xxxx.

SELECT first_name, last_name, REGEXP_REPLACE( phone, '(\d{3})\.(\d{3})\.(\d{4})', '(\1) \2-\3' ) phone_number FROM employees ORDER BY phone_number;
Code language: SQL (Structured Query Language) (sql)

The following illustrates the output:

Oracle REGEXP_REPLACE function example

The meaning of the regular expression ([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4}) is as follows:

In this tutorial, you have learned how to use the Oracle REGEXP_REPLACE() function to replace a sequence of characters that matches a regular expression pattern with another string.

Was this tutorial helpful?