Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions / Oracle REGEXP_REPLACE

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 ] ] ] ] )

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;

The following is the result:

This is a demo of REGEXP_REPLACE function

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;

Here is the output:

402**********8590

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;

Output:

This line contains more than one spacing between words

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;

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?
  • YesNo
Previous Oracle REGEXP_SUBSTR
Next Oracle REGEXP_LIKE

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.