Oracle REGEXP_REPLACE Function

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 n^th 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 functionCode 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 ]

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)

Try it

Here is the output:

402**********8590Code 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)

Try it

Output:

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

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

D) Reformat phone numbers

See the following employees table in the sample database:

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)

Try it

Output:

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

Summary #

• Use the Oracle REGEXP_REPLACE() function to replace a sequence of characters that matches a regular expression pattern with another string.